Need to split cell info based on text start and end points

thetruestorey · March 16, 2022, 4:50pm

I am hoping someone can help me with splitting text from 1 cell into 3 different cells.
On the attached spreadsheet, I need to copy text from each cell in column H and split it into the below. pasting the output into columns I, J and K.

Everything from the word Evidence down to (but not including) the word Workaround
Everything from the word Workaround down to (but not including) the word Root
Everything from the word Root down to the end of the text in that cell
Test.xlsx (10.9 KB)

ppr · March 16, 2022, 4:57pm

We can do with e.g. with regex

EVIDENCE(.|\n)*?(?=WORKAROUND)

[CheatSheet] - System.Text.RegularExpressions | RegEx - News / Tutorials - UiPath Community Forum

thetruestorey · March 18, 2022, 11:48am

Thank you for that.
I’ve never used Regex before so would I use this inside the Excel Application Scope and after read range?
Also, would using a ‘for each’ be able to work inside this also?

Any example flow you could provide would be much appreciated.

ppr · March 18, 2022, 12:37pm

give a try on following:

read range
For each row in datatable | row - dtData

Assign: row(I-NameOrIndex) = Regex.Match(…, Pattern1).Value
Assign: row(J-NameOrIndex) = Regex.Match(…, Pattern2).Value
Assign: row(K-NameOrIndex) = Regex.Match(…, Pattern3).Value

Write range

ensure that i,j,k column is already present or add it to the datatable before the for each row

thetruestorey · March 22, 2022, 10:31am

I am struggling to get the data read from the excel spreadsheet to then split and paste into a new sheet.
I keep getting errors because it does not allow implicit conversions between datatable and iEnumerable