1

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

Reck
  • 81
  • 6

2 Answers2

4

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

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
0

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)

Spectral Instance
  • 1,971
  • 2
  • 3
  • 6