Extract a specific text from a field

[griseldagomes]

Hello Experts,

I am looking for a way to extract a specific text which starts with 'R1' followed by 6 characters (total character length = 8) from field 'Reference'.

unique no.	Reference
1	text 1 hello R14388AB
2	dummy R19584PE
3	6548 R1T1258X test
4	checkfile R19865WW
5	R1E9556S tester

Below is the expected output

unique no.	Reference	output
1	text 1 hello R14388AB	R14388AB
2	dummy R19584PE	R19584PE
3	6548 R1T1258X test	R1T1258X
4	checkfile R19865WW	R19865WW
5	R1E9556S tester	R1E9556S

Can you please resolve this query.

Thank you

[danilang · Accepted solution]

hi @griseldagomes 

you can use the following expression in a Regex tool set to parse 

.*?(R1.{6}).*

The capture group is (R1.{6}) which looks for the exact string R1 followed by any 6 characters

Dan

[cjaneczko]

The following should work in a formula tool

REGEX_Replace([Reference], '.*\b(R1\S+).*', '$1')

[flying008]

Hi, @griseldagomes 

FYI.

(?:^|\s)(R\d+\S+)(?=\s|$)

[griseldagomes]

Thank you @danilang @cjaneczko @flying008 for taking time out to respond to my query.

[Alpha325]

Thanks @flying008

What about I want to drop "R1" as well and only keeps the number after that? Thanks. 

[flying008]

Hi, @Alpha325 

So, what about other letter like 'WW' or 'T' after drop 'R1' ? If you only want the number :

(?:R1\D?)(\d+)\S+(?=\s|$)

[Alpha325]

Thank @flying008 I should have clarified more. Using this case as an example, I want to keep everything after "R" including numbers, special characters such as "-" till the next space in the content. Example could be - for example "R12515-52125-21521 Denver" and the length could vary. And I only want to carve out "12515-52125-21521". Thanks!

[flying008]

[Alpha325]

Thanks @flying008. This helps a lot. But for some reason, when I run it, it always missed /dropped the very last digit. Do you know why? Thx.