Extracting Zip Codes from an address string.

[Bullflip]

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?

 

[mrshl9898]

=IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,9))),RIGHT(A1,9),RIGHT(A1,5))

 

[MrsFixIt]

=IF(ISNUMBER(NUMBERVALUE(RIGHT(A1,9))),RIGHT(A1,9),RIGHT(A1,

 

[MrsFixIt]

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]

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

 

[Bullflip]

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]

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]

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]

another approach

Zip
15317
15317
374216000
374216000
374216000
25526
659730
10707
973015397
14202
08837
27607
38119
78701
30047
30047

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]

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