Regex: Extract

[HW1]

Hi,

I have a text as:

15/12/20 | JOB-2783166-B2D7 120L COVID19 Waste Bin Service 1 91.80 91.80

I want to extract the values as:

Date	Job number	Description	Action	Qty	Rate	Total
15/12/2020	JOB-2783166-B2D7	120L COVID19 Waste Bin	Service	1	$  91.80	$  91.80

I am trying REGEX  but I am unable to get the correct values.

Can you please help with the regex pattern?

Thanks

[HW1]

So I am trying 

\JOB-([0-9]+-[^\s]+).

But it does not extract JOB but just extracts 2783166-B2D7 and misses the JOB part totally.

What's the correct regex?

[Tyro_abc · Accepted solution]

hi..

How about this?  but it is difficult to test because there is only one row in your data

In my expression, I put  

(\d+/\d+/\d+) \| ([A-Z0-9\-]+) (.*) (Service|Something) ([0-9|\.]+) ([0-9|\.]+) (.*)

if you expect more than one value in Action field, please replace "Something" with that.

Best Regards

Arundhuti

[BretCarr]

I LOVE LOVE LOVE REGEXing.

I got your one line to work with:

(\S*)\s?\|\s?([\w\S]*)\s([\s\S]*)\s(\d*)\s([\d\.]*)\s([\d\.]*)

I don’t know the other patterns since I’m working on my phone, so it’s hard to know if this will work or if there are variations to adjust for.

[BretCarr]

Since I’m not on my computer, can you show us your pattern?

(\S*)\s?\|\s?(JOB[\w\S]*)\s([\s\S]*)\s(\d*)\s([\d\.]*)\s([\d\.]*)

works, too if JOB is always there.

[Tyro_abc]

I tried your formula, worked like charm.

[HW1]

The rest of all the fields as required are parsed except the Action column. I am really really bad at regex and I need help with it almost every single time.

Thank you for your support

[BretCarr]

I liked your date parsing. I’ve never tried the + before. I tend to use {1,2} quantifiers when not going greedy.

[BretCarr]

Last post, promise!

In your original post, you don’t have the parentheses including the “JOB-” part which would leave that part of your parse.