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
Solved! Go to Solution.
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?
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
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.
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.
I tried your formula, worked like charm.
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
I liked your date parsing. I’ve never tried the + before. I tend to use {1,2} quantifiers when not going greedy.
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.