Convert a string to a date?

[Terry Pinnell]

I have a series of entries in col A like this:

Tuesday 7th September 2004
Tuesday 3rd April 2007
etc.
FWIW, all happen to start 'Tuesday' in the present case, although a
fully general solution would be ideal in case I come across
non-Tuesday variations in future.

Is there a simple way to get these into a proper date format please?
I'd be happy with various types, my aim being to abbreviate them. So
for example

Tue 7 Sep 2004
7/9/07
or even
Tuesday 7th Sep 2004

would be OK. Obviously, once I have them in Date format, I can
experiment with the alternatives.

An alternative I suppose would be to do a global Replace. But that
would need repetitions, to change 'January' to 'Jan', February' to
'Feb', etc.

 

[Guest]

Text to Columns, format each column then concatenate.

 

[macropod]

Hi Terry,

For a 'date' in A1, try:
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1)+1,LEN(A1)),"st",""),"nd",""),"rd",""),"th",""))
and format the result as a date.

Cheers

 

[Terry Pinnell]

Hi Terry,

For a 'date' in A1, try:
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND(" ",A1)+1,LEN(A1)),"st",""),"nd",""),"rd",""),"th",""))
and format the result as a date.

Cheers

Thanks both, I'll try those suggestions.