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

Help Studio
, ,
1

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

We can do with e.g. with regex

grafik
grafik759×397 23.8 KB

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

grafik
grafik791×621 27.7 KB

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

3

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.

4

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

5

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

6

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

7

Hi @thetruestorey,

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

8

One way i try it, I get this error

image
image1050×665 26.1 KB

image
image1014×230 6.3 KB

Another way, i get this one

image
image933×278 10.4 KB

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.

9

Flow:

grafik
grafik692×777 57 KB

grafik

we also did trim the value additionally

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