Extracting a date from a line of data

Katy H

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.

Paul Newcome

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.

Paul Newcome

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.

Katy H

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

Paul Newcome

Happy to help. 👍️