How to get a Zip Code from an address

Hi.

In all of your examples, the string ends in "##### USA", where "#####" is the ZIP code. I presume, then, that this is always the case (otherwise you would surely have given some examples of different layouts)?

Regards

Thanks.

=LEFT(RIGHT(A1,9),5)

Or, if you need the result to be a numeric:

=0+LEFT(RIGHT(A1,9),5)

Regards

Hi Drgre,

Here is a Control+Shift+Enter (CSE) array formula that will do what you need.

=MAX(IFERROR(MID(A1,ROW(A1:A80),7)+0,""))

See the attached.

Originally Posted by MarvinP

=MAX(IFERROR(MID(A1,ROW(A1:A80),7)+0,""))

Not sure I understand, Marvin. The OP already confirmed that the format was fixed and identical for each string. Hence we can simply locate the ZIP code in the same place it will always be.

Besides, what happens if there's another, larger number in the address somewhere? Granted, unlikely. But impossible?

Or what does your formula return for e.g.:

41 January Street, FLUSHING, NY 11355 USA

?

Regards

Hey Xor LX,

The formula looks down the string for 7 characters in a row that are completely numbers. My assumption is that there is a space in front and behind the 5 digit Zip code. I could have keyed on the "USA" but just wanted to show a different method. Here is another way using strings and now the "USA"

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1)*(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),LEN(A1)))

After putting my formulas into the cell, instead of pressing enter, you need to hold the Control and Shift keys down and then press Enter. This makes it an Array Formula.

To tag this thread as "Solved" you need to EDIT your original post, click on "Go Advanced" and change the prefix of the thread to "Solved".

Originally Posted by MarvinP

Hey Xor LX,

The formula looks down the string for 7 characters in a row that are completely numbers. My assumption is that there is a space in front and behind the 5 digit Zip code. I could have keyed on the "USA" but just wanted to show a different method.

Sure, but it's not at all guaranteed to work in all cases! That was my point!

Regar