Trying to extract the city from a street address in a cell

[Ray J]

I ave a report with a few thousand entries where the street address, city, state and ZIP are listed in one cell. I need to grab the city name from the middle of the cell.

Examples:
7A Highland Way Kingston MA 02364
PO Box 582AB-12 Keanu HI 96749
17895 Lake Mendo Dr Mirage Island CA 92270
89576-86 Kahauloa Rd Captain Hook HI 96704

The City name varies in length and may have more than one word in the name. The Street is variable as well. The state is always two letters and the ZIP always 5 numbers. The is a single space between each data point.
I've been able to pull the ZIP and the State into separate columns, but I am stuck with the city. Is the a way to use MID and LEN to do this? Or VBA?

 

[ghrain22]

I ave a report with a few thousand entries where the street address, city, state and ZIP are listed in one cell. I need to grab the city name from the middle of the cell.

Examples:
7A Highland Way Kingston MA 02364
PO Box 582AB-12 Keanu HI 96749
17895 Lake Mendo Dr Mirage Island CA 92270
89576-86 Kahauloa Rd Captain Hook HI 96704

The City name varies in length and may have more than one word in the name. The Street is variable as well. The state is always two letters and the ZIP always 5 numbers. The is a single space between each data point.
I've been able to pull the ZIP and the State into separate columns, but I am stuck with the city. Is the a way to use MID and LEN to do this? Or VBA?

It's tough since the data isn't very consistent. What you could do (since everything ends with a zip code of the same length) is you could find a list of zipcodes w/ cities and do a lookup. You could probably also figure out how to query the zipcode to a database, but I don't know much about that area.

 

[shawnhet]

Unfortunately, there is no simple way to split the city out from this sort of data AFAIK.

However, since you have the ZIP Code, you can download a zip code database - see the free one here -

ZIP Code Database - ZIP Code List

and then use a VLOOKUP using the Zip Code something like the following:

=VLOOKUP(D2,zip_code_database!A:C,3,FALSE)

Cheers,