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

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.

MrsFixIt

New Member
Joined
Apr 15, 2016
Messages
18
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
Joined
Feb 6, 2012
Messages
1,951

ADVERTISEMENT

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

Bullflip

New Member
Joined
Jun 2, 2020
Messages
16
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 6, 2012
Messages
1,951

ADVERTISEMENT

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
Joined
Jun 2, 2020
Messages
16
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? For example:

27 Van Orden Placehackensacknj07601

State is NJ
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,497
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
 
Learn Excel from Bill Jelen

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

Forum statistics

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