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

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
PLEASE HELP
0
rfedorov
Asked:
rfedorov
  • 5
  • 3
  • 3
  • +1
3 Solutions
 
etech0Commented:
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
 
rfedorovAuthor Commented:
etech0:thank you for your respond, but looks like you did not read my post careful...
0
 
nap0leonCommented:
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
rfedorovAuthor 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
 
etech0Commented:
whoops - my error.
Try this one:
=MID(A1,LEN(A1)-13,6)
0
 
Saqib Husain, SyedEngineerCommented:
Try this

=MID(A1,LEN(A1)-7-IF(MID(A1,LEN(A1)-7,1)="-",5),5)
0
 
nap0leonCommented:
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
 
etech0Commented:
=MID(A1,LEN(A1)-13,6)
assuming that it will always end with a space and then US
0
 
nap0leonCommented:
@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
 
rfedorovAuthor Commented:
i got it myself,

=TRIM(MID(K2,(FIND("CA ",K2,1))+3,5))
0
 
nap0leonCommented:
@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
 
Saqib Husain, SyedEngineerCommented:
nap0leon,

Here is the file with mine and your formulas
Extract-Zip.xls
0
 
nap0leonCommented:
@ssaqibh - you're right.
When I copied and pasted, I had a trailing " " at the end of the string ("US ").
Your function works great.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 5
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now