Remove trailing whitespace

[mb1824]

Hi,

I have a trial balance exported from source system in .csv

I have tried the Data Cleansing tool with Remove "Leading & Trailing Whitespace" checked, however it doesn't work. 

So I suspect while it appears to be a trailing whitespace to me, Alteryx is reading it as something else. I am having a similar issue with this data source in Tableau.

If I also check "All Whitespace" within the Data Cleansing Tool, the apparent 'trailing whitespace' is removed but so are all the spaces in my account names

Any ideas?

[MarqueeCrew · Accepted solution]

Please try:

Regex_replace([field],'[^\x20-\x7E]','')

Cheers,
Mark

[mb1824]

Thank you, that worked

[MarqueeCrew]

Btw, that removes all characters outside of the printable ascii character set.

[mb1824]

Thanks for that. How would I apply the same to 7 columns without 7 RegEx tools?

[MarqueeCrew · Accepted solution]

Use a multi-field formula on the current field.

[RogerA]

Hello, i have been trying to get this to work too, but it's not.  My data may be slightly different in that i have a regex earlier on in the flow which is removing text between brackets so from:

AB1234 (XY23RG) Widget

to

AB1234  Widget

I think in doing this using \(.*?\) it's leaving a trailing space as i have two spaces inbetween now.  Like the original poster the data cleanse tool is not getting rid of it, and the suggested regex here doesn't appear to work either.

[BenMoss]

Could you try replace([field],’ ‘,’’)

Thought the behaviour you describes makes me think that this isn’t actually whitespace as in fact some funky character that looks like whitespace.

What I would do is take a sample like and use the text input to right that value out, as you write it out you know you are using the space key, then perform a join, if they don’t match then it’s some strange chars that are causing the issue.

Ben

[BenMoss]

Also Roger, if you are using the ‘multfield’ method, make sure you have the ‘copy output as new field’ option checked as this can often fool people into thinking that it isn’t working when it is, because they don’t acknowledge it has gone into a new field!

[RogerA]

Thanks, looking at the data a bit more my issue is definitely caused by the removal of the text in brackets. Cells where there is no brackets works fine.