# Extracting Zip Codes from an address string.

#### Bullflip

##### New Member
Hi,

I am trying to extract zip codes from a string in Excel that has no delimitors (i.e. no space, no comma, etc.) It's just all mixed together. In some cases the zip codes are 5 digit and some are 9 digits. Is there a formula to extract the numbers at the end no matter what length?

#### Attachments

• pic 1.JPG
97.9 KB · Views: 13

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### mrshl9898

##### Well-known Member
=IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,9))),RIGHT(A1,9),RIGHT(A1,5))

#### MrsFixIt

##### New Member
my zipcode is always 5 digits or not there so my answer may not have numbers, and I want a default answer of 99999 when it is not a number

#### mrshl9898

##### Well-known Member

Try =IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,5))),RIGHT(A1,5),99999)

#### Bullflip

##### New Member
Hi, thank you for the formula, it worked. If you mind, can you please explain the logic in the formula, I would like to try to understand it. Again, thank you!

#### mrshl9898

##### Well-known Member

Sure, RIGHT(A1,9) is grabbing the last 9 characters, the result will always be text (999999999 or aaaa99999). NUMBERVALUE will convert them from text to a number if they are all numeric. ISNUMBER then sees if it is a number (999999999 will now be aaaa99999 will be text still having failed the NUMBERVALUE conversion).

ISNUMBER will give a TRUE or FALSE result, IF is then used to give the desired result based on the TRUE/FALSE outcome. =IF(test,true result, false result)

#### Bullflip

##### New Member
One last question, how can I modify the formula to extract the first 2 letters from the right, which has the abbreviated state? For example:

27 Van Orden Placehackensacknj07601

State is NJ

#### sandy666

##### Banned - Rules violations
another approach
 Zip 15317 15317 374216000 374216000 374216000 25526 659730 10707 973015397 14202 08837 27607 38119 78701 30047 30047

Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Split = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Column1", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
NFT = Table.AddColumn(Split, "Custom", each try Number.FromText([Column1]) otherwise null),
TSR = Table.SelectRows(NFT, each ([Custom] = null)),
ZIP = Table.AddColumn(TSR, "Zip", each Text.Select([Column1], {"0".."9"})),
Filter = Table.SelectRows(ZIP, each ([Zip] <> "")),
TSC = Table.SelectColumns(Filter,{"Zip"})
in
TSC``````

#### mrshl9898

##### Well-known Member
=IF(ISNUMBER(NUMBERVALUE(MID(A1,LEN(A1)-5,1))),MID(A1,LEN(A1)-10,2),MID(A1,LEN(A1)-6,2))

Replies
14
Views
561
Replies
7
Views
823
Replies
12
Views
294
Replies
2
Views
187
Replies
4
Views
196

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

1,151,608
Messages
5,765,402
Members
425,282
Latest member
Nibblesy

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