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.Reference1text 1 hello R14388AB2dummy R19584PE36548 R1T1258X test 4checkfile R19865WW5R1E9556S tester Below is the expected output unique no.Referenceoutput1text 1 hello R14388ABR14388AB2dummy R19584PER19584PE36548 R1T1258X test R1T1258X4checkfile R19865WWR19865WW5R1E9556S testerR1E9556S Can you please resolve this query. Thank you

Extract a specific text from a field

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

Regex

Accepted answers

danilang

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

All comments

danilang

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

录制_2024_01_16_09_40_12_354.gif

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

录制_2024_03_21_08_32_58_567.gif

Alpha325

Thank @flying008I 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!