Extracting a date in a non standard format

[JaneMore]

Hello I am trying to extract a date of birth date string held in a cell and put it in a standard US date format in a new cell eg 12/21/1903

The date is being held in a text cell in the following format but is some times surrounded by other data

DOB-12-21-1903 Bill Married Mary on 10-07-1924
DOB 04-25-1911
DOB-02-28-1925 John went to war in 03-07-1939 but did not return till 03-07-1946
DOB 09-12-1928
DOB 09-12-1906 Mary Past away in 1976

Some times the data string can be prefixed with "DOB" or some times its "DOB-" this is how I am able to tell that this is this persons date of birth

I am not sure of the best method to go about trying to extract the date of birth please could someone help me

 

[Fluff]

How about
=SUBSTITUTE(TRIM(MID(A2,SEARCH("dob",A2)+4,11)),"-","/")*1

 

[Joe4]

For an entry in cell A1, this formula woudl return that value as Text (12/21/1903).

=SUBSTITUTE(MID(A1,5,10),"-","/")

If you need it to be a date, surround it with DATEVALUE, i.e.

=DATEVALUE(SUBSTITUTE(MID(A1,5,10),"-","/"))

then apply your desired custom date format.

 

[Rick Rothstein]

If you need it to be a date, surround it with DATEVALUE, i.e.

=DATEVALUE(SUBSTITUTE(MID(A1,5,10),"-","/"))

I don't know if it is local dependent or not, but DATEVALUE works directly without the SUBSTITUTE function call on my (US locale) system...

=DATEVALUE(MID(A1,5,10))

 

[Joe4]

I don't know if it is local dependent or not, but DATEVALUE works directly without the SUBSTITUTE function call on my (US locale) system...

=DATEVALUE(MID(A1,5,10))

Nice! That's one less step...