Using Regex to edit datatable values

[sam.willson]

Hi there,

I’m currently having issues trying to edit values in my datatable using regex.

I have worked out that the following enables me to get the data from my datatable column I need, but I’m unsure how to do this for every row in my datatable: System.Text.RegularExpressions.Regex.Match(“(?<=Work ID:\s)(\d+)”).Value.Trim

Please would anyone be able to help?

Attached is my example datatable, and I’ve also attached my desired output.

Thanks in advance

Datatable - Edited .xlsx (8.7 KB)
Datatable - Original.xlsx (8.7 KB)

[ppr (Peter)]

We assume that the excel was read ranged into a datatable - e.g. Variable name: dtData

then try the following:

• For each row /for each row in datatable activity - row in dtData
  • Assign Activity:

row("Column 1") = System.Text.RegularExpressions.Regex.Match(row("Column 1").toString,"(?<=Work ID:\s)(\d+)").Value.Trim

aftwerwards write back dtData (we recommend to do it on another worksheet) to Excel e.g with write range activity

[sam.willson]

Hi @ppr thanks so much for that, that looks to have worked perfectly! I appreciate the quick response