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

Pull out zip code?

I have addresses in a cell that is the below format. How do I pull out just the zip code?

2725 Agoura Rd, Thousand Oaks, CA 91361
0
cansevin
Asked:
cansevin
  • 3
1 Solution
 
bbaoIT ConsultantCommented:
if the addresses are for US only and the zip codes are always at the end of the addresses after their two-letter state name, you may simply extract the 5-digit zip codes by using string functions.
0
 
ProfessorJimJamCommented:
=UPPER(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)))
0
 
ProfessorJimJamCommented:
in case if you want to pull the two digit State, let me save you time , and cover it also in this thread to pull only State CA  then here is formula   =UPPER(MID(A1,FIND(",",A1,1)+2,2))
0
 
Glenn RayExcel VBA DeveloperCommented:
Cool function, ProfessorJimJam, for pulling out the zip code.    

No points, but....
The function to pull the state code doesn't work (shows first two letters of city in uppercase), but this formula will show the state code:
=RIGHT(LEFT(A1,LEN(A1)-LEN(TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)))-1),2)

Note that it uses the LEN of the result found for the zip code as part of this.

-Glenn
0
 
ProfessorJimJamCommented:
Thank you Glenn.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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