Converting long text dates into short dates

[walneybird]

I have a spreadsheet that stores the dates in long format eg 23rd June 1995 - and I need to convert them into short dates eg 23/05/1995

I have tried to reformat them and it makes no difference at all - even tried using the custom format. Ive managed to get it to display 23/j/95 but am really struggling to get it to convert the month into a number

Any ideas on how I can do this

Thanks in advance

 

[SteveO59L]

Sounds like what you believe to be dates, are actually text.

 

[VoG]

Try

=DATEVALUE(LEFT(A1,2)&RIGHT(A1,LEN(A1)-4))

 

[barry houdini]

....but that won't work if the day is a single digit like

8th April 2011

perhaps try

=REPLACE(A1,FIND(" ",A1)-2,2,"")+0

format in required date format

 

[walneybird]

Thank you so much Barry it worked a treat

Much appreciated