Tool Mastery | RegEx

[Rosaile]

Good Afternoon,

I'm trying to parse data in a column that has somewhat of a pattern to it, but can be entered several different ways.  The important pieces of data from these lines are the 2 to 3 letter initials (MWC, NR, etc) and the "Q* Year" portions.  The rest of the data is not necessarily needed, but I was hoping i could just split all the data out into pieces.  If the data was uniform I think I could write something fairly easy, but with this being a large amount of data and the data being consistently out of order, possibly missing data (ex. Q_2017 missing number part of the qtr).  I wasn't sure if there was a way for the expression to look forward across the whole line and look back. etc.

The first line I can pull the date doing something like ^(\d*\W*\d*\W*\d*), but that doesn't work for line 2, 3, & 4.  Any suggestions?

Ex.

line 1:           11-21-16 MWC - Q1_2017 savings; $123,456

line 2:           MWC  6/10/18 - Q2 2018 savings; $123,456

line 3:           NR 5/1/2017- Q 2014 $123,456 savings.

line 4:           Q4_2010 $123,456 savings; CM 1-1-11

Thanks!

[Thableaus · Accepted solution]

Hi @Rosaile 

Could you please try this out?

Initials:

REGEX_Replace([Field1], ".*?([A-Z]{2,}).*", "$1", 0)

Q Year:

REGEX_Replace([Field1], ".*?(Q\d?.?\d{4}).*", "$1", 0)

Cheers,

[Rosaile]

Wow!  Thank you as that appears to have done exactly what I wanted for all the data.  I'm going to spend the next couple hours trying to reverse engineer what you did so I can understand it going forward.  Thank you very much.