Extracting a date from a line of data
I have been working on formulas to automate data extraction from a string of data that is used to identify lines of data being exported from equipment and I am struggling with how to extract the date and convert it into a regular date format. Any help would be appreciated!
Example line of data: A3146 GAT 26MAY22 CCV21
The date is bolded, I have figured out how to extract everything else and know I will need to use helper columns. I know how to convert the date but not day and year.
Best Answer

If you are already able to extract the string, you would do something like this...
=DATE(VALUE("20" + RIGHT(extraction_formula, 2)), month_options_below, VALUE(LEFT(extraction_formula, 2)))
This is assuming that the days will always be two digits so that 1 is actually "01".
You are going to want to drop your extraction formula into the two places where it says "extraction_formula".
For the "month_options_below" portion you have two options. You can either write out a nested IF statement to grab the month text and output the appropriate month number or you can create a reference table to has the month text in one column and the month number in another and use an INDEX/MATCH to pull in the appropriate month number.
Answers

If you are already able to extract the string, you would do something like this...
=DATE(VALUE("20" + RIGHT(extraction_formula, 2)), month_options_below, VALUE(LEFT(extraction_formula, 2)))
This is assuming that the days will always be two digits so that 1 is actually "01".
You are going to want to drop your extraction formula into the two places where it says "extraction_formula".
For the "month_options_below" portion you have two options. You can either write out a nested IF statement to grab the month text and output the appropriate month number or you can create a reference table to has the month text in one column and the month number in another and use an INDEX/MATCH to pull in the appropriate month number.

As always, thank you @Paul Newcome! This helped me do the math in my head to make it work!

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!