Need help to extract particular reference from the file.

[jerometyl]

OUR REF :A000688YOUR REF : SCEI/004/FY17 dated 23.01.2018

OUR REF :A000687YOUR REF : CG/010/FY18 dated 28.11.2018

OUR REF :A000686YOUR REF : ETPL/005/2016 dated 30.10.2018PO AMENDED as at 8 March 2019

OUR REF : ETPL/002/FY18YOUR REF : ETPL/002/FY18DESIGN-AND-BUILD

I need help to extract out the data SCEI/004/FY17 and CG/010/FY18 and ETPL/005/2016 and ETPL/002/FY18

Any idea how i can do so? I have over thousands of lines 

[alisonpitt · Accepted solution]

Hi @jerometyl, the regex you need is (?<=YOUR REF \: )[A-Z]*\/\d{3}\/[A-Z0-9]{4}

Spoiler

[messi007]

@jerometyl,

Here you go : 

Attached the workflow,

Regards,

[rfoster7]

looks like your required pattern is 2-4 upper case characters followed by a / followed by three digits followed by a / followed by either four digits or FY + 2 digits, yes?

regexp tool to the rescue

One row didn't match because it doesn't match the pattern of the other rows. QN/2018/JAN/6023R3

you could do a regexp pattern of something like "any number of characters not including a space, followed by a \ followed by any number of characters not including as space, followed by a \, followed by any number of characters ending in a space" or something like that if you have other non standard pattern data to find. 

https://regex101.com/ This website lets you build a regexp pattern and check it against test data. its been invaluable to me. 

[jerometyl]

Thank you @alisonpitt it works perfectly. 

[jerometyl]

OUR REF : PR8070YOUR REF : LOA T/CG/13/FY19This PO is to be read with SCEI PO 4700095881.

Hi, can i check what is the regex formula to extract "T/CG/13/FY19"?

Some of the data ref have a "T" infront.