What is the shortest formula in excel to covert text date format e g 17 Jan 2023 into regular date format like 1 17 2023

Question

I have sales data with text indicating the transaction date, such as "17 January 2023." I wish to use the TEXT(A1,"MMM") or MONTH(A1) functions to extract the month name, however, these two functions don't work with text date formats.

I tried numerous formulas to change the text date format to the standard date format, but I was unable to find the optimal little function or formula.

Later, I attempted the method below, but it is too lengthy and intricate to utilize.

=DATE(RIGHT(A1,4),MATCH(MID(A1,1,3),{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)-1+1,DAY(LEFT(A1,FIND(",",A1)-1)))

My question is if anybody knows are the shortest way to do the same.

Answer 1

The SUBSTITUTE() method can be used to eliminate the, that exists in your text, and then nesting this in the VALUE() function should yield an Excel-recognised date, i.e., 17 January 2023. However, you must render the date format manually if you are using an English-language locale.

=VALUE(SUBSTITUTE(A1,","," "))