Text to Columns issue

[rmartinez4]

Hi All,

I tried using text to columns to separate a text data in a cell by a comma delimiter but its not catching all the comas to delimit. Is there away around it? Why is Alteryx missing the commas?

Thank you for the help in advanced.

[Robin_McIntosh]

@rmartinez4 Have you tried increasing the columns to 99 and then using a Data Cleanse tool as @ChrisTX mentioned?

[rmartinez4]

Yes, That works why is that the case?

[Robin_McIntosh · Accepted solution]

The tool is instructed to only find and split out the first 3 comma delimiters.  All commas after that are not handled and left in the last split column (or dropped all together) depending on setup.  By increasing the number of columns to split into, you're telling the tool to only split out the first X comma delimiters.  If you know there will always be say 7 commas, then you set it and forget it.  But if it will always vary and you don't know what the max would be, set a higher number and then delete all the null columns afterward.

Not sure if this is what you're looking for in terms of how it works.

[rmartinez4]

Yes, your answer makes sense. Thank you for the explanation. 

[ChrisTX]

In the Text to Columns tool, the value in Number of Columns is important, but we may not know the correct number to use.

To get an idea of the number of commas in a field, you could use a Formula tool with code like this:

   REGEX_CountMatches([your field], "\,")

Chris