• Status: Solved
• Priority: Medium
• Security: Public
• Views: 254

HELP MY WITH FUNCTION

i HAVE A STRING , AND I NEED TO EXTRACT THE ZIP CODE
THE PROBLEM IS SOME ZIP IS 5 DIGITS AND SOME ZIPS IS 9 DIGITS INCLUDING DASH
1925 GREEN CANYON RD /CANOGA CA 93510 US
29412 SMITH DR /WEST HILLS CA 91301-4238 US

I NEED JUST 5 DIGITS ZIP
0
rfedorov
• 5
• 3
• 3
• +1
3 Solutions

Commented:
put
=LEFT(A1,5)
in the next column (assuming A1 is the first cell with a zip code), and copy it all the way down.
0

Author Commented:
etech0:thank you for your respond, but looks like you did not read my post careful...
0

Commented:
etech0 - your answer gives only the leftmost 5 characters of the string.
he needs the 5 digit zip code.

Can we presume that all addresses end with "US"?
Can we presume that all addresses contain a 2 character state abbreviation?
0

Author Commented:
1925 GREEN CANYON RD /CANOGA CA 93510 US ------> 93510
29412 SMITH DR /WEST HILLS CA 91301-4238 US ------> 91301

yes for all question
0

Commented:
whoops - my error.
Try this one:
=MID(A1,LEN(A1)-13,6)
0

EngineerCommented:
Try this

=MID(A1,LEN(A1)-7-IF(MID(A1,LEN(A1)-7,1)="-",5),5)
0

Commented:
ssaqibh's is close...

works on the second example, not on the first
1925 GREEN CANYON RD /CANOGA CA 93510 US       3510
29412 SMITH DR /WEST HILLS CA 91301-4238 US       91301

Try this one:
=MID(A1,LEN(A1)-8-IF(MID(A1,LEN(A1)-7,1)="-",4),5)

Seems to work for the two examples:
1925 GREEN CANYON RD /CANOGA CA 93510 US       93510
29412 SMITH DR /WEST HILLS CA 91301-4238 US      91301
0

Commented:
=MID(A1,LEN(A1)-13,6)
assuming that it will always end with a space and then US
0

Commented:
@etech0
your formula's results yield the first 6 characters of the rightmost 13 characters.  This is good in the zip+4 scenario, but does not handle when just plain "zip5" is provided.
1925 GREEN CANYON RD /CANOGA CA 93510 US       A CA 9
29412 SMITH DR /WEST HILLS CA 91301-4238 US       91301
0

Author Commented:
i got it myself,

=TRIM(MID(K2,(FIND("CA ",K2,1))+3,5))
0

Commented:
@author
As long as you will always have California addresses and none of those addresses contain the text "CA " as, for example a street name, then that would work.
The other solutions are more holistic and will apply to addresses from any state.

example where yours will fail:
123 CA DRIVE /WEST BEVERLY HILLS CA 90210-1234            DRIVE
0

EngineerCommented:
nap0leon,

Here is the file with mine and your formulas
Extract-Zip.xls
0

Commented:
@ssaqibh - you're right.
When I copied and pasted, I had a trailing " " at the end of the string ("US ").