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?

 

[Bullflip]

Thank you both for your help!

 

[Peter_SSs]

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
	A	B	C
1	7 Van Orden Placehackensacknj07601	07601	NJ
2	2000 Corporate Drivecanonsburgpa374216000	374216000	PA
Zip Code

Cell Formulas
Range	Formula
B1:B2	B1	=RIGHT(A1,5+4*ISNUMBER(RIGHT(A1,9)+0))
C1:C2	C1	=UPPER(MID(A1,LEN(A1)-LEN(B1)-1,2))

 

[sandy666]

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

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

 

[sandy666]

Thank you both for your help!

You are welcome, glad we could help
Have a nice day