Excel: Formatting text into a recognisable date

Question

I have a .csv file with many rows where the date is formatted as you see in column A. I manually copied and then changed the date to how I want it to be in column B where it's also recognised by Excel as a date (and not text).

My question is does anyone know how I can get Excel to format the date like this automatically via some forumula magic or some other method?

Thanks

Answer 1

Another formula that should return the correct date. This does require Office 365:

=--TEXTJOIN(" ",,INDEX(TEXTSPLIT(SUBSTITUTE(TRIM(A1),",","")," "),,{2,1,3,5}))

This removes the , and then rearranges the order. The -- turns it into a number.

If the month abbreviations do not match the local settings it will not work

---

For older versions:

=--(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",999)),999,999)) & " " &
TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",999)),1,999)) & " " &
TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",999)),1998,999)) & " " &
TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,",","")," ",REPT(" ",999)),3996,999)))

Answer 2

Shorter formula for older versions:

=--SUBSTITUTE(REPLACE(A2,1,8,MID(A2,5,2)&"/"&LEFT(A2,3)&"/")," @ "," ")

(English-locale)

=--SUBSTITUTE(REPLACE(A2,1,8,MID(A2,5,2)&"/"&FIND(LEFT(A2,3),"  JanFebMarAprMayJunJulAugSepOctNovDec")/3&"/")," @ "," ")

(non-English locale)