community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Remove trailing whitespace

Asteroid

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?

Alteryx Certified Partner
Alteryx Certified Partner
Please try:

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

Cheers,
Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

Thank you, that worked

Alteryx Certified Partner
Alteryx Certified Partner
Btw, that removes all characters outside of the printable ascii character set.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

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

Alteryx Certified Partner
Alteryx Certified Partner
Use a multi-field formula on the current field.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Asteroid

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.

Alteryx Certified Partner
Alteryx Certified Partner
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
Alteryx Certified Partner
Alteryx Certified Partner
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!
Asteroid

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.

Labels