Split Concatenated values into distinct rows

Hi,

The data in the excel file sheet1 contains the different columns with the concatenated values in each column. I now need to split each value by using the delimiter comma (,) into distinct rows like the output shown in the below screenshot.

I tried using Text to Columns tool splitting each value into multiple columns then do the transpose and then filter out if there is any empty value and then do the cross tab. But, no luck. I'm not getting the desired output.

Output Output

Therefore, guide / help me with the right approach with the logic to get the exact output as expected. Output data is also present in the excel file.

Also, let me know if there is any other approach to achieve this result without using Text to columns tool.

Thanks & best regards,

stj

xx.xlsx

New Workflow2.yxmd

Workflow

Transformation

Preparation

Accepted answers

Andrew-Merrill-CoG

Here is a workflow that doesn't use Text to Columns Tool to parse data. You do have problematic fields since the [amt] and [Est] columns have extra "," in them for decimal separation. I removed them in the workflow provided before the split and added them back after the fact. On of the most efficient ways to perform this operation is to use Generate Rows and Multi-Field Formula Tools:

Hope this helps and Happy Solving!

Sample Workflow.yxmd

flying008

Hi, @stj1120

FYI.

录制_2025_04_23_11_24_22_187.gif

All comments

Andrew-Merrill-CoG

Hope this helps and Happy Solving!

Sample Workflow.yxmd

flying008

Hi, @stj1120

FYI.

录制_2025_04_23_11_24_22_187.gif

stj1120

Hi @Andrew-Merrill-CoG

Thank you for your help, it is working.

Best regards,

Stj.

stj1120

Hi @flying008

Thank you for the help. Could you provide the workflow of your approach?

Best regards,

Stj

stj1120

Hi @Andrew-Merrill-CoG

I need your help again. In case, the dataset has some concatenated values in few columns like Typ, Code and Amt and other columns don't have. In this case, how do I split the concatenated values and show them in the distinct rows? Input dataset is in the first sheet of the attached excel file. Second sheet is the output data.

Kindly check the data and provide the right approach with the data as shown in the output sheet.

Thanks & best regards,

stj.

New Workflow2.yxmd

xx.xlsx

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280