easy one: Formula to extract date in string using 2007

[chrissooo]

Hi

I'd imagine this would be an easy one but I'm trying to extract the date in the following string:

<TABLE style="WIDTH: 1046pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1389><COLGROUP><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 4835" width=147><COL style="WIDTH: 52pt" span=18 width=69><TBODY><TR style="HEIGHT: 14.95pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: white; WIDTH: 1046pt; HEIGHT: 14.95pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 height=20 width=1389 colSpan=19>Round 1, Thursday, 24th March 2011



</TD></TR></TBODY></TABLE>
The date should always begin on the 2nd character after the 2nd comma (working left to right).

Just want the date, not the day of the week, so result wanted would be: "24th March 2011"

Thanks in advance

 

[texasalynn]

try this

Excel Workbook
	A	B
20	Round 1, Thursday, 24th March 2011	24th March 2011
21	Round 10, Thursday, 24th March 2011	24th March 2011
Sale

Excel 2003

Cell Formulas
Range	Formula
B20	=RIGHT(A20,LEN(A20)-FIND("|",SUBSTITUTE(A20,",","|",2))-1)

 

[chrissooo]

Brilliant...thanks texasalynn and thx for the quick reply.

 

[chrissooo]

Just wondering if there is a way to format the destination cell in the format "24/03/2011"?

I have manually formatted the cell (right click-format cells -date etc) but it is still "24th March 2011"

As it is going into a db, I'd prefer to have the former format.

Thanks

 

[Peter_SSs]

Try

=TEXT(REPLACE(REPLACE(A1,1,SEARCH("day, ",A1)+4,""),FIND(" ",REPLACE(A1,1,SEARCH("day, ",A1)+4,""))-2,2,""),"dd/mm/yyyy")

 

[chrissooo]

Thanks Peter..that is spot on. Thank-you very much.