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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

schielrn

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

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,941
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,941
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))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,851
Messages
5,833,967
Members
430,249
Latest member
Muka

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