Trim Whitespace from Text to Columns Results

[mcarrico]

Quick and (hopefully) simple one here.  Many times when I use the Text To Columns tool, there is still leading whitespace from comma separated lists with spaces.  It would save from the minor inconvenience of putting a Data Cleansing tool after each of these tools to include an Advanced Option to trim whitespace from the results.  I tried some workarounds using the empty fields and including spaces in the delimiters box, but this results in unwanted behavior.

[MarqueeCrew]

Trim(replace([Divisons],", ",','))

If before you parse the field, divisions, you were to replace all commas followed by a space with just a comma (or better yet a PIPE |) then you wouldn't need that data cleansing tool.

Here are some of the ideas that I might ask for in this tool:

• Since Alteryx has 1 record in memory, instead of # default to 3, have Alteryx put the right number based upon that first record.
• Embed the select options to at minimum remove the original field from output
• Convert the output to a data type of your choosing

Cheers,

Mark

[mcarrico]

Thanks for the recommendation Mark.  Agreed that there are a number of ways to achieve this data cleansing, but I find myself doing this over and over so was just hoping for a quick convenience enhancement.  I would second your enhancements as well.

[JoshKushner]

@mcarrico I agree with you. It is intuitive that items concatenated by columns are 'technically' separated by a comma and a space.

If we were to represent this text to columns logic in REGEX it would be: ,\s?

[mcarrico]

Yes, except the spaces are optional so I would add a '0 or more qualifier' to the space specification (i.e. ,\s*)