Parse text 1 row into multiple rows

[jimmytpnguyen]

Hi there,

I'd like to read text from input data then based on the values convert into multiple rows, see below.

Thanks,

Jimmy

Input Data
[5510,5511,5551,6701,6700,8544,5512,5513]
Output Data
5510
5511
5551
6700
8544
5512
5513

[ctthornb123]

I'm not sure if this needs to be dynamic but I think you just need to clear the brackets using a replace formula, then use text to columns tool to split to columns by using the comma as a delimiter.  Then transpose all the data and clear out the nulls.  I set the text to columns tool to 25 columns but this can be adjusted depending on the number of records you may need to account for in the data.  However, the # of columns cannot exceed 1000 so we will need to look at a different solution if that's the case.

See attached workflow

[danilang · Accepted solution]

Hello @jimmytpnguyen

The solution the @ctthornb123 provided will work, but there is a more dynamic way to split to rows.  Use the Text To Columns tool and configure it to split to rows.  This will place each item on a new row with out requiring the transpose tool. This is also an option with the RegEx tool.

To remove the brackets you can use Trim(field1,"[]") in a formula tool.

See the attached for the complete workflow.

Dan

[jimmytpnguyen]

Great! thank you so much.