Extracting Numbers from Cell

octord

New Member
Joined
Apr 12, 2010
Messages
16
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!
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
If what I said is true, try this:
Excel Workbook
AB
144246 W Fullerton Ave Chicago IL 60639-205060639-2050
Sheet2
Cell Formulas
RangeFormula
B14=MID(A14,FIND("~",SUBSTITUTE(A14," ","~",LEN(A14)-LEN(SUBSTITUTE(A14," ",""))))+1,LEN(A14))

Hope that helps.
 
Upvote 0
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))
 
Upvote 0
I have many cell that need to do the same and the content of each cell is different LEN.
 
Upvote 0
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.
 
Upvote 0
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!!
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top