Split excel cell values separated by delimiter to multiple rows

Hi all, I want to split excel cell values which are separated by delimiter “|” to multiple rows within the same excel. Tried string manipulation but that doesn’t work, any idea how I can get a solution to it?

Thanks in advance

@Shailesh123,

Can you share a sample data of your excel, is that in a single cell or from multiple cells in a same row?

@sarathi125commaseperated
can you refer the screenshot. If you can see in Col-2 I have values seperated by “|”. So i want to split those into multiple rows. So if row 1 has 3 values seperated by “|”, my excel should have 3 rows with Col-1, Col-2 and Col-3 proper in it. Was i clear in my explanation?

@Shailesh123,

So are you expecting the output like the following,

image

@sarathi125 - yes exactly . Any idea, how it can be achieved?

@Shailesh123,

Check the attached xaml and excel which I have used, it is working as expected. You can check and let me know if any issues.

SplitExcelCellValues.xaml (10.9 KB) Shailesh123.xlsx (9.6 KB)

@sarathi125- what if I have 114 columns and over 100 rows in my excel, Should I manually add all rows in Add data row activity ? like row(0),row(1)…so on till 100 rows.

1 Like

@Shailesh123,

Do we need to check all the 114 columns for the separators…?

@sarathi125- Yes since its a part of the template. So we need all columns.

@Shailesh123,

Can you please confirm on the below image, do we have the separator “|” in all the columns of data or in any one of the column.

No @sarathi125, I have seperator only in one column , but then I have 114 columns which are all required in my excel example :

@Shailesh123,

Check the updated workflow file and let me know if any issues.

SplitExcelCellValues.xaml (12.1 KB)

1 Like

@sarathi125-thanks it worked. !! I added columns manually as there were only 43 , I did not clone the datatatable.

1 Like

Please mark the solution post as “Solution”. Thanks