Datatable column split and Remove data row


I have an Excel sheet that I would like to separate the text from a column. In column A I would like to retrieve the text that is in between the red lines shown on the image below and store it into another column. As my goal is to only have 2 columns: 1) The text in red brackets in one column and 2) The values in the next cell in column B.

image d

I wasn’t sure in this case if it would be best to remove the unnecessary rows first (1-9). Then split the text in column A, and pull only the 2 columns that I need from that but I’m unsure how to split these cells.

Any input would be appreciated, thanks!

Hi @Striggs,

Currently UiPath doesn’t have any activities to split column and create another column. But we have work around.
Before we go technical,As per my understanding you will have fixed notation to identify the required text in the column.
If yes use like below

  1. read range -excel
  2. build datatable with specific column names
  3. use parallel foreach activity
  4. apply string manuplations to get required text from column 1
  5. use add datarow to add to datatable
1 Like

Hello @venkat4u

Once reading the range , how would I go about deleting the first 9 rows as seen in the image? I’m not sure how to use RowIndex for multiple rows for Remove data row. This file is created with these additional rows on top, but are not to be included. I figured this would be better than using Select Range as the row amounts can vary based on the criteria generating the report.

In this test file attached I tried to pull the string (WOM/NA0070) from the first column but in this case I was only getting “6*” out of the entire cell that is composed of “6* PRJ WOM/NA0070 PCH Insignia

SplitExcelCol.xaml (14.7 KB)

Would a column have to be created for each separation occurring in the string or am I able to retrieve just the section that I would like?

can you share the excel sheet.
do you have any headers for the excel sheet. in the sample image i can not see any headers.