White Space

[reginawhelan]

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?

[JosephSerpis]

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

[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. 

[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? 

[JosephSerpis · Accepted solution]

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

[reginawhelan]

Thank you! Works perfectly.

[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. 

[MichaD]

Thanks for the quick response, Joseph.

These are indeed good reasons which I was not fully aware of : )

Regards,

Michael