Exracting Date from string

[Son]

Hi, I have a string, which looks like the following phrase, and i need to extract the first date value that appears in it. The dates are, usually, preceeded by the character :, as it is shown in the example. In this case, i need to have 1/4/2011. Any ideas would be most appreciated!


JOHN ATE AN APPLE ON: 1/4/2011. THEN HE GOT HOME AND SLEPT. HE WOKE UP AT: 2/4/2011. TWO DAYS BEFORE, IE ON 31/3/2011 HE HAD GONE SKIING. AND TOMORROW HE WILL GO AGAIN.

 

[shadow12345]

if that was written in a1 then this formula in B1 would get you the date.

=TEXT(MID(A1,FIND(":",A1,1)+2,9),"dd-mm-yyyy")

 

[T. Valko]

Hi, I have a string, which looks like the following phrase, and i need to extract the first date value that appears in it. The dates are, usually, preceeded by the character :, as it is shown in the example. In this case, i need to have 1/4/2011. Any ideas would be most appreciated!


JOHN ATE AN APPLE ON: 1/4/2011. THEN HE GOT HOME AND SLEPT. HE WOKE UP AT: 2/4/2011. TWO DAYS BEFORE, IE ON 31/3/2011 HE HAD GONE SKIING. AND TOMORROW HE WILL GO AGAIN.

Try this...

=DATEVALUE(MID(SUBSTITUTE(A2,"."," "),FIND(":",A2)+1,11))

Format as Date

 

[Son]

Biff, great!!! Your formula did the trick!!! I had tried to apply the mid formula, but i had not gone far, especially with substituting the . with "". Thanks again!

Shadow, thank you very much for your suggestion, i'm only going with Biff's because your formula returns the "." at the end.

my thanks to both of you!

 

[T. Valko]

Biff, great!!! Your formula did the trick!!! I had tried to apply the mid formula, but i had not gone far, especially with substituting the . with "". Thanks again!

Shadow, thank you very much for your suggestion, i'm only going with Biff's because your formula returns the "." at the end.

my thanks to both of you!

You're welcome. Thanks for the feedback!