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.