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!
Solved! Go to 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,
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.