# 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

#### Bullflip

##### New Member
Thank you both for your 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

#### Peter_SSs

##### MrExcel MVP, Moderator
One last question, how can I modify the formula to extract the first 2 letters from the right, which has the abbreviated state?
You could also try this (which includes a shorter alternative for the zip code extraction)

20 11 13.xlsm
ABC
17 Van Orden Placehackensacknj0760107601NJ
22000 Corporate Drivecanonsburgpa374216000374216000PA
Zip Code
Cell Formulas
RangeFormula
B1:B2B1=RIGHT(A1,5+4*ISNUMBER(RIGHT(A1,9)+0))
C1:C2C1=UPPER(MID(A1,LEN(A1)-LEN(B1)-1,2))

#### sandy666

##### Banned - Rules violations
extract the first 2 letters from the right,
 State Zip PA 15317 PA 15317 TN 374216000 TN 374216000 TN 374216000 WV 25526 YM T59730 NY 10707 OR 973015397 NY 14202 NJ 08837 NC 27607 TN 38119 TX 78701 GA 30047 GA 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"})),
Len = Table.AddColumn(ZIP, "Length", each Text.Length([Zip]), Int64.Type),
Filter = Table.SelectRows(Len, each ([Zip] <> "")),
Add = Table.TransformColumns(Filter, {{"Length", each _ + 2, type number}}),
Upper = Table.TransformColumns(Last,{{"stzip", Text.Upper, type text}}),
Space = Table.AddColumn(Upper, "State Zip", each Text.Insert([stzip],2," ")),
TSC = Table.SelectColumns(Space,{"State Zip"})
in
TSC``````

#### sandy666

##### Banned - Rules violations
Thank you both for your help!
You are welcome, glad we could help
Have a nice day

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,612
Messages
5,765,419
Members
425,286
Latest member
CazzaBabes

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