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!
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
So is it safe to assume you need everything after the last space?
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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.
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
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

New Member
Joined
Apr 12, 2010
Messages
16

ADVERTISEMENT

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

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
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

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

Well-known Member
Joined
May 9, 2009
Messages
16,623
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,383
Messages
5,601,318
Members
414,441
Latest member
KellyTheKid

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
Top