Eliminate invisible whitespace/tabs?

[celinearamouni]

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 Code	Length
124	4
145	4

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

[BS_THE_ANALYST]

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

[celinearamouni]

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

[BS_THE_ANALYST]

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

formula:

[FinnCharlton · Accepted solution]

Hi @celinearamouni ,

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

[celinearamouni]

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]

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

[binuacs]

@celinearamouni One way of doing this