extract date from string

i know this is probably a really simple thing to do but i am really not familiar with text worksheet functions. anyways i have a cell which contains the string: "Transfer Date: 3/19/2006". i want to extract only the date from the string e.g. "3/19/2006".

note: if it was March 1st, then it would show "Transfer Date: 3/1/2006"...so i cant use the right function since i dont know the exact length of the date.

Try this:

For A1 containing text in the format you described
A1: Transfer Date: 3/19/2006

B1: =--TRIM(MID(A1,FIND(":",A1)+1,255))
Format B1 as a date.

Does that help?

Regards,
Ron

kdp

Select cells, Data/Text to Columns/Fixed Length.
Separate words and space from date, format as date
You can choose to not import the text part.

HTH
Beege


"kdp145" <[email protected]> wrote in
message news:[email protected]...
>
> i know this is probably a really simple thing to do but i am really not
> familiar with text worksheet functions. anyways i have a cell which
> contains the string: "Transfer Date: 3/19/2006". i want to extract only
> the date from the string e.g. "3/19/2006".
>
> note: if it was March 1st, then it would show "Transfer Date:
> 3/1/2006"...so i cant use the right function since i dont know the
> exact length of the date.
>
>
> --
> kdp145
> ------------------------------------------------------------------------
> kdp145's Profile:
> http://www.excelforum.com/member.php...o&userid=29594
> View this thread: http://www.excelforum.com/showthread...hreadid=518715
>

thnx Ron..that worked perfectly