Solved

# HELP MY WITH FUNCTION

Posted on 2011-10-26
250 Views
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
Question by:rfedorov

LVL 10

Assisted Solution

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 Comment

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

LVL 18

Assisted Solution

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 Comment

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

yes for all question
0

LVL 10

Expert Comment

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

LVL 43

Accepted Solution

Try this

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

LVL 18

Expert Comment

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

LVL 10

Expert Comment

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

LVL 18

Expert Comment

@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 Comment

i got it myself,

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

LVL 18

Expert Comment

@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

LVL 43

Expert Comment

nap0leon,

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

LVL 18

Expert Comment

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

## Featured Post

This very simple solution applies to a narrow cross-section of the "needs to close" variety. In this case, the full message in Event Viewer was in applog, Event ID 1000: Faulting application iexplore.exe, version 8.0.6001.18702, faulting module …
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.