Split excel cell values separated by delimiter to multiple rows

Shailesh123 · November 25, 2019, 11:19am

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

sarathi125 · November 25, 2019, 11:31am

@Shailesh123,

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

Shailesh123 · November 25, 2019, 11:37am

@sarathi125 commaseperated
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?

sarathi125 · November 25, 2019, 11:42am

@Shailesh123,

So are you expecting the output like the following,

Shailesh123 · November 25, 2019, 11:43am

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

sarathi125 · November 25, 2019, 12:06pm

@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)

Shailesh123 · November 25, 2019, 12:15pm

@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.