Extracting Numbers from Cell

[octord]

I have the following content in one cell

<TABLE style="WIDTH: 373pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=497><COLGROUP><COL style="WIDTH: 373pt; mso-width-source: userset; mso-width-alt: 21205" width=497><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 373pt; HEIGHT: 11.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=15 width=497>4246 W Fullerton Ave Chicago IL 60639-2050
</TD></TR></TBODY></TABLE>
Need to extract the zip code from cell....

I worked on it with the following but no luck:

=MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

I get 4246 W Fuller which is a sign of heading in some kind of directions but not the right one...Please help!

 

[schielrn]

So is it safe to assume you need everything after the last space?

 

[schielrn]

If what I said is true, try this:

Excel Workbook
	A	B
14	4246 W Fullerton Ave Chicago IL 60639-2050	60639-2050
Sheet2

Cell Formulas
Range	Formula
B14	=MID(A14,FIND("~",SUBSTITUTE(A14," ","~",LEN(A14)-LEN(SUBSTITUTE(A14," ",""))))+1,LEN(A14))

Hope that helps.

 

[T. Valko]

I have the following content in one cell

<TABLE style="WIDTH: 373pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=497 border=0><COLGROUP><COL style="WIDTH: 373pt; mso-width-source: userset; mso-width-alt: 21205" width=497><TBODY><TR style="HEIGHT: 11.25pt" height=15><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 373pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" width=497 height=15>4246 W Fullerton Ave Chicago IL 60639-2050

</TD></TR></TBODY></TABLE>
Need to extract the zip code from cell....

I worked on it with the following but no luck:

=MID(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0),COUNT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))

I get 4246 W Fuller which is a sign of heading in some kind of directions but not the right one...Please help!

Try this:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

 

[octord]

I have many cell that need to do the same and the content of each cell is different LEN.

 

[schielrn]

So do neither solutions provided work? If not, you will need to post a little more sample data because they both work with your one sample line.

 

[octord]

Thank you!! Both solutions work just fine.... there was only one minor situation where the data, since it was an extract, had a blank space at the end but a quick trim and it works... Thank you for saving me tons of time... very much appreciated!!

 

[T. Valko]

Thank you!! Both solutions work just fine.... there was only one minor situation where the data, since it was an extract, had a blank space at the end but a quick trim and it works... Thank you for saving me tons of time... very much appreciated!!

This version will account for strings with trailing spaces.

=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),100))