# Extracting Numbers from Cell

#### octord

##### New Member
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)))

### Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
So is it safe to assume you need everything after the last space?

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.

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)))

Try this:

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

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

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.

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!!

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))

Replies
14
Views
731
Replies
2
Views
693
Replies
13
Views
250
Replies
7
Views
559
Replies
4
Views
503

1,203,181
Messages
6,053,964
Members
444,695
Latest member
asiaciara

### 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.

### Which adblocker are you using?

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

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