Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Eliminate invisible whitespace/tabs?

celinearamouni
6 - Meteoroid

Hello everyone,

 

I have this list of project codes that should be 3 characters only but shows up as 4 because of an invisible tab or whitespace that I do not see. I have used the data cleansing tool multiple times in a row but nothing is work. This is what I am getting.

 

Project CodeLength
1244
1454

 

I need the length to be 3. I have attached the list. I believe the whitespace/whatever it is/tab is on the left because when I use the left() formula it cuts off the last number. Ideally, I wouldn't use the right() formula as these rows are included in a big data sets with project codes longer than that and doing so would cut them off.

 

Thank you

7 REPLIES 7
BS_THE_ANALYST
13 - Pulsar

Have you tried the function trim? :-) . I attached an example for you to try and implement. 

BS_THE_ANALYST_0-1673946623030.png

BS_THE_ANALYST_1-1673946629458.png

BS_THE_ANALYST_2-1673946637106.png

 

 

 

celinearamouni
6 - Meteoroid

Yes! I have and it didn't work. Also tried replace([Project Code]," ","") didn't work either...

BS_THE_ANALYST
13 - Pulsar

hmm, try this formula instead: REGEX_Replace([Field1], "^(\s+)?(.*\w)(\s+)?$", "$2")

 

formula:

BS_THE_ANALYST_0-1673947112640.png

 

 

FinnCharlton
13 - Pulsar

Hi @celinearamouni ,

I don't know what invisible character you have here but here is is a way to get rid of it.

FinnCharlton_0-1673947195369.png

 

celinearamouni
6 - Meteoroid
OMG! Thank you so much. It is the only that has worked so far. I'm not familiar with that tool so will definitely look into it. :)
FinnCharlton
13 - Pulsar

Also @celinearamouni , the character is a Zero-Width Space, Unicode-8203.

binuacs
20 - Arcturus

@celinearamouni One way of doing this

 

binuacs_0-1673949603713.png

 

Labels