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
Can you share a sample data of your excel, is that in a single cell or from multiple cells in a same row?
@sarathi125
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 - yes exactly . Any idea, how it can be achieved?
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
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.
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 :
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