isolate date in text field

[NSA0135]

Is there a way to find and isolate a date in an excel text field. I have to isolate a date within the text cell and use this date in another type of calucaltion.

 

[edokhotnik]

What format is the text and date? Can you post a sample, please?

 

[NSA0135]

Format is as follows: status changed closed to open: John Smith: 2/23/2011 : failed status update: late posting

 

[MDuff]

Kinda made this one ourt of some toher posts may be a better way but seemed to work

Excel Workbook
	A	B
1	status changed closed to open: John Smith: 2/23/2011 : failed status update: late posting	2/23/2011
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=DATEVALUE((LEFT(MID(MID(MID(SUBSTITUTE(A1," ","^",7),1,256),FIND("^",SUBSTITUTE(A1," ","^",7)),256),2,FIND(" ",MID(MID(SUBSTITUTE(A1," ","^",7),1,256),FIND("^",SUBSTITUTE(A1," ","^",7)),256))),9)))

 

[meldoc]

Hello and welcome

Or maybe this, there will definitely be better than this.

Excel Workbook
	A	B
1	status changed closed to open: John Smith: 23/02/2011 : failed status update: late posting	23/02/2011
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=MID(A1,MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},A1&1234567890)),SEARCH(" ",A1,MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},A1&1234567890)))-MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},A1&1234567890)))+0

 

[T. Valko]

Format is as follows: status changed closed to open: John Smith: 2/23/2011 : failed status update: late posting

Try this...

=LOOKUP(1E100,--MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),ROW(INDIRECT("1:"&LEN(A2)))))

Format as Date.

 

[Haseeb Avarakkan]

Just a change in the T.Valko's formula to avoid INDIRECT volatile function

=LOOKUP(1E+100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5,6,7,8,9,10}))

 

[T. Valko]

Just a change in the T.Valko's formula to avoid INDIRECT volatile function

=LOOKUP(1E+100,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),{1,2,3,4,5,6,7,8,9,10}))

That's a generic formula to find any number in a string.

Since this request is specifically for the DATE, and assuming that there are no other numbers in the string, then we might be able to use something as simple as:

=--TRIM(MID(A2,FIND("/",A2)-2,10))

That works on the sample string that was posted.

 

[NSA0135]

Thank you all. The next to last entry was the item which worked form and has saved me several hours of reviewing each cell.