need help splitting a name form a date

[neowok]

basically I have a column of name+date i.e. "John Smith 05/01/06". What
I need to do in a dummy column is put a formula that returns only the
date.

The problem I have is that some of the dates are in format 05/01/06 and
some are 05/01/2006 so the number of characters for the date is not
consistent (so i cannot just use the Right runction).

anyone know a way of doing this?

thanks

 

[Bob Phillips]

=MID(A11,FIND("~",SUBSTITUTE(A11," ","~",LEN(A11)-LEN(SUBSTITUTE(A11,"
",""))))+1,99)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Guest]

=RIGHT(A1,LEN(A1)-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND(" ",A1))
will give you the date as text

=DATEVALUE(RIGHT(A1,LEN(A1)-FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1)))-FIND("
",A1)))
will give you the date as an excel date (format as date to show a date)

You could also use the Data->Text to Columns function. Select delimited and
use a space as the delimiter.

 

[neowok]

thanks thats worked nicely.