Formula to Extract Date (MM/DD) within Inconsistent Text Strings


#1

Hi, everyone!

I need to devise a formula that can extract the date from large numbers of text strings that look (more or less) like this:

Purchase authorized on 02/08 Starbucks Card Rel 800-782-7282 WA S466038798107217 Card XXX

Purchase with Cash Back $ 20.00 authorized on 02/08 Vons Store 2665 Los Angeles CA P00466040145047099 Card XXXX

Purchase authorized on 02/09 Arco #42938 Los Angeles CA P00466040490070986 Card XXXX

Purchase authorized on 02/08 Rudy’s Barbershop S Los Angeles CA S286040133024078 Card XXX

Purchase authorized on 02/09 Lyft *Ride Mon 7 Lyft.ME CA S386041115222697 Card XXX

I guess the formula would have to FIND the first occurrence of the “/” symbol and return the two characters that come before and after it but from there I get lost… Any ideas, team?

:slight_smile: all the best!


#2
MID(myString, FIND("authorized on", myString) + 14, 5)

Should do the trick…


#3

BTW: this does the same thing, but I personally don’t prefer searching for just a ‘/’…

MID(myString, FIND("/", myString) - 2, 5)