Data manipulation excel - extract specific text from a string

Help Activities
, ,
1

Hi all,

I am trying to extract only a number from an cell stored in an excel file (attached). The cell content looks like that:

"NEFROLIFE ***********
J40/15046/2011
ROONRC.J40/15046/2011
26672692

Bucureşti Sectorul 2, Şos. ŞTEFAN CEL MARE, Nr. 236, Bloc 59B1, Scara A, Etaj 3, Apartament

The pattern is: after the ROONRC, the second “/”, after the first 4 numbers
Forum .xlsx (8.9 KB)
. In our case the output should be 26672692.

Thank you in advance for your support.
Paul

2

maybe a regex can serve:
grafik
with reference to the group 1
grafik

3

Hi @paul.baltag1 ,

You could also Check with the below Regex Expression :

(?<=ROONRC).*\/\d{4}(\n*.*)

We access Group 1 here to get the Required value :

image
image1082×359 38.1 KB

Let us know if you still require further help.

4

Hi,

First, thank you for helping me.
The expression seems to work (i think this is representing the yellow mark-up) but when I try to extract it into a variable and write it on a excel it’s exporting this:

System.Linq.Enumerable+d__97`1[System.Text.RegularExpressions.Match]

It’s a problem with the variable type?

Best regards,
Paul

2
21334×654 17 KB

1
11179×751 74.7 KB

5

YourMatchesOutputVariableVar(0).Value
YourMatchesOutputVariableVar(0).Groups(1).Value

6

@paul.baltag1 ,

If for an Input the occurrence of the value is always once, we could go with the Match instead of Matches like below :

System.Text.RegularExpressions.Regex.Match("YourInputVar","(?<=ROONRC).*\/\d{4}(\n*.*)",System.Text.RegularExpressions.RegexOptions.IgnoreCase).Groups(1).Value.ToString

If there are many occurrences of the value to be extracted, we would go for Matches Activity and loop through the values from the Output and Store as Required.