Excel Workbook | ||||
---|---|---|---|---|
A | B | |||
1 | status changed closed to open: John Smith: 2/23/2011 : failed status update: late posting | 2/23/2011 | ||
Sheet1 |
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))) |
Excel Workbook | ||||
---|---|---|---|---|
A | B | |||
1 | status changed closed to open: John Smith: 23/02/2011 : failed status update: late posting | 23/02/2011 | ||
Sheet1 |
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 |
Try this...Format is as follows: status changed closed to open: John Smith: 2/23/2011 : failed status update: late posting
That's a generic formula to find any number in a string.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}))