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

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.

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

We can do with e.g. with regex


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

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

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.

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

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

will have a look on it, just give me a little time

Hi @thetruestorey,

What error are you getting can you show that, the solution given by @ppr should definitely work

One way i try it, I get this error


Another way, i get this one

Apologies if I am totally doing this wrong, completely new to Regex and anything like that, so i am going into it a bit blind.

Flow:


grafik

we also did trim the value additionally

Find starter Help here:
ppr_ExcelBox_thetruestorey.xaml (9.3 KB)