Split comma separated text to columns(by dynamically checking no of delimiters in text)

pk1

Hi,

I have text column (only 1 Row data)(comma separated) need to split into columns, but here problem is I want to change (# split into no of columns) in "Text to Columns" tool dynamically.

For example: if have A,B --> number should change to 2 dynamically, by counting delimiters in text

                                 A,B,C ->  number should change to 3 dynamically, by counting delimiters in text

* I have no idea whether Regex can do this

Can any one help me on this

Thanks,

Kartheek

bharti_dalal

Hi @pk1,

I have a solution for your question, in order to dynamically run Text to Column tool, you would have to create a Batch Macro and with the help of some formula, you would be able to dynamic your output.

• Create a new column using a formula tool and use formula - REGEX_CountMatches([Fruits], ",") + 1. It would give the count of delimiters available in a string.  See Screenshot.

• Create a batch macro, under Updated Value, select @value-value = "3" and check mark "Replace a specific string". I have attached workflow both macro and workflow for your reference.
• You would have to use summarize tool to find out the max value for fields to split, i.e. max number of columns
•  now attach the macro  and see the results. A screen shot of the result is also attached here.

Let me know if you have any concerns. Happy Learning.

dynamic texttocolumn.yxzp

texttocolumn.yxzp