White Space

Hi, I am having a hard time removing White Space from all the columns within my flow. I have tried the following:

- Dynamic Rename Tool - trim([_currentfield_])

- Data Cleansing Tool - Remove Unwanted Characters: Leading and Trailing WhiteSpace

- Multi-Field Formula - trim([_currentfield_])

The following chart shows how the data could be and my desired results:

Original	Desired
Hello	Hello
Good Day	Good Day
Thanks (with white space at the end)	Thanks

Does anyone have any ideas on how to remove unwanted white space?

Developer Tools

Accepted answers

JosephSerpis

Hi @reginawhelan got it work using the multi field tool I've attached my workflow.

White_Space_21052020.yxmd

All comments

JosephSerpis

Hi @reginawhelan I mocked up a workflow let me know what you think?

White_Space_21052020.yxmd

CharlieS

Hi @reginawhelan

I would suggest using two functions to achieve this: Trim( and RegEx_Replace(. Trim you already mentioned, will remove the whitespace at the beginning and end. To replace repeated whitespace within the string, we can use RegEx to achieve this. The RegEx pattern to identify one or more space characters is "\s+", so if we replace those occurrences with just a space character " ", then we've eliminates repeating whitespace characters within the string.

We can put both of these expressions together in one formula:

Trim(REGEX_Replace([Original],"\s+"," "))

Give that a try and let me know if that works for you.

20200521-Whitespaces.yxmd

reginawhelan

Thank you. It works using the Formula function but not a Multi-Field Formula. Do you know a quick way to add this formula to 97 columns?

MichaD

Solved perfectly, but I was wondering if it wouldn't have worked with the Data Cleansing Tool, too?

If I tagged "Tabs, Line Breaks, and Duplicate Whitespace" in the "Remove Unwanted Characters" section of the tool (in addition to the standard "Leading And Trailing Whitespace"), I get the same results.

@JosephSerpis , @CharlieS , do you have any reasons for not choosing this "basic" tool? Just curious... ; )

JosephSerpis

The data cleanse tool does not have dynamic or unknown fields option so if additional fields are added you would need to manually select them. The Data cleanse tool is one of the worse performing tools in Alteryx so using regex with the Multifield tool is a more efficient solution and allows it be dynamic if the data expands.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703