Excel convert TEXT (28th April 2017) TO DATE

Question

I have some dates in my excel spreadsheet that have th,nd,st and rd in the string so I am currently struggling to convert them to a date format of dd/mm/yyyy.

How would I be able to do this using a formula? 28th April 2017 = 28/04/2017

Thanks

Answer 1

With a formula:

=--(LEFT(A1,MIN(SEARCH({"th","st","nd","rd"},A1 & "thstndrd"))-1)&" " & MID(A1,MIN(SEARCH({"th","st","nd","rd"},A1 & "thstndrd"))+3,LEN(A1)))

This is an array formula and needs to be confirmed with Ctrl-Shift-Enter.

Then format as dd/mm/yyyy

Answer 2

You could give a try with this formula as well,

=IF(ISNUMBER(VALUE(LEFT(A1,2))),TEXT(REPLACE(A1,3,2,""),"mm/dd/yyyy"),TEXT(REPLACE(A1,2,2,""),"mm/dd/yyyy"))

The formula finds the numeric value in first two places and formats the date accordingly. The advantage of this formula is, you can modify the mm/dd/yyyy format as per your needs. There would not be a necessity to change the format of the cell.

Answer 3

The text being in A1:

=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"th",""),"st",""),"nd",""),"rd",""))