Extracting Zip Codes from an address string.

Bullflip

New Member
Joined
Jun 2, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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
    pic 1.JPG
    97.9 KB · Views: 13

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
Joined
May 28, 2005
Messages
52,251
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
7,497
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}}),
    Last = Table.AddColumn(Add, "stzip", each Text.End([Column1], [Length]), type text),
    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
 
Learn Excel from Bill Jelen

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

Forum statistics

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