Removing Spaces Between Text for Different Fields/Columns

[kpbhangal]

Hi,

I have a spreadsheet with information on applications (description, codebase, vendor product / home-grown, data center, provider, etc.) I used the trimleft and trim right functions to clean up leading and trailing spaces throughout the spreadsheet. However, some cells within certain columns have spacing errors other than leading or trailing spaces. For example, some cells in the platform column are written as Linux/ OracleDB when I'd like it to say Linux/Oracle DB. There are a bunch of these spacing errors in multiple columns (platforms is one, data center is another).

I tried using the Find / Replace tool to fix this but it appears I can only fix the errors in one column? Please let me know if there a way to add additional columns to fix in the Find / Replace tool or if you have another method of achieving this

Thank you!

[atcodedog05]

Hi @kpbhangal 

Use data cleansing tool.

You can remove leading, trailing and duplicate space on multiple columns with this.

https://help.alteryx.com/current/designer/data-cleansing-tool

Hope this helps 🙂

[kpbhangal]

Thanks @atcodedog05! The data cleansing tool helped accomplish some of what I need. However, I'm still running into the problem where if I tell the data cleansing tool to remove leading/trailing/all white space, the format for cells like the ones I'm showing below gets altered. For example, although Linux/ Oracle DB becomes Linux/OracleDB (like I want), Windows Xmarain becomes WindowsXmarain (one word, which is not what I want). Is there a way to work around this? Additionally is there a way to insert punctuation (such as a comma between elements of a list within a cell) for only specific cells?

Thanks again!

[atcodedog05 · Accepted solution]

@kpbhangal 

This seems like more of custom changes

You can use leaverage Replace() function in formula tool. You can do a nested Replace

And you can use Multi Field formula tool to apply it on multiple columns.

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Multi-Field-Formula/ta...

Hope this helps 🙂

[Tyro_abc]

Hi @kpbhangal

You can check with the data cleansing tool and check the "tab, lines brakes, duplicate whitespace" check box.

Hope it would help 😊

[JTSJr]

The data cleansing tool is not available when using IN DB commands,