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

sample.xlsx

Regex

Accepted answers

alisonpitt

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

Spoiler

All comments

alisonpitt

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,

Reg_20220513yxmd.yxmd

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.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335