City State Zip Extraction

[mkane12]

what is a good formula to extract a city, state and zip? Some have commas, some don't. Some cities are more than one word too which makes it not work for some.

Here is what I've done but it only works when it is a one worded city with a comma:

example:
Gurnee, IL 60031
=Left(B1,SEARCH(",",B1)-1)
---> result : Gurnee

example:
San Diego, CA
-->what is a formula I can use with the above one? Maybe throw in an IF statement to do with the spaces?

 

[Joe4]

Welcome to the Board!

A few questions:
- Are you trying to extract City, State and Zip into 3 different cells?
- Do the entries just have City, State, and Zip and not anything else (i.e. no names or street addresses or country codes)?
- Are the zip codes always 5 digits, or may there be some extended zip codes (9 or 10 digits)?
- Are the states always the 2 digit abbreviation?
- Can you show us a representative sample of different variations, so we make sure we account for all the different variations?