Best tool to clean data of spaces besides trim and data cleanse

[hi2019]

So I have data that I am using a if contains /replace a certain value and it is only working for some so I assume that it is due to some spacing possibly? I cannot share data due to hippa

I tried TRIM and data cleanse and its still only picking up some with the contains/replace formula

Any sugestions

[abe_ibanez]

I would probably use the Data Cleansing tool to remove Leading and Trailing Whitespace, Tabs, Line Breaks, and Duplicate Whitespace. 

If you are still having issues, you can also modify the case to make everything upper/lowercase in case you are using case sensitive formulas. 

 

[TUSHAR050392]

Have you tried using Find Replace tool to replace values contained in a string as that will take care of the spaces as well? Check out the documentation for this tool and you will get an idea from the examples.

Thank you

[CoG]

I recommend Regex since you can check for more cases (for your actual use case) or use it to get rid of duplicate spaces in conjunction with Trim to prep your data for parsing (via another round of Regex!)

If you are able to share some sample data (not a sample of your data, but dummy data that portrays your issue) that would allow us to better assist. 

[KGT]

You're concentrating here on ways to replace spaces. I assume the text is human readable the same as in you can't see any difference between them but they still won't join.

Replacing Spaces:

• If it's duplicate spaces: REGEX_Replace([Field],'\s+',' ')
• If it's at the end/start: Trim ([Field])

Troubleshooting:

If that's not working, then it may not be spaces you're trying to replace. If there is another character in there that you can't find, get the problematic data and try this:

• REGEX Tool Expression: . (that is just a full stop, nothing else)
• Tokenise to new rows.
• Formula to create a new field: CharToInt([Field])

That will give you the Integer value of each character and tell you what you need to replace.