excelexcel-formulaexcel-dates

Excel: Formatting text into a recognisable date

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

enter image description here

Solution

  • 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

    enter image description here

    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)))

    enter image description here