Extract City and State from address

[scojax22]

I have a database of full addresses, including city and state in 1 cell. I want to add two new columns for city and state, and need a formula for each - I am using Left(Find",") to extract the address and Right(5) for the zip - but the city and state are giving me problems.

Any help would be great.

Ex: 101 Main Street, Cherry Hill, NJ 08510

 

[Jonmo1]

Welcome to the board

With the address in A2

Street: =LEFT(A2,FIND(",",A2)-1)
City: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",255)),255,255))
State: =MID(A2,LEN(A2)-7,2)
Zip: =RIGHT(A2,5)

 

[scojax22]

Awesome. No idea what the City formula did, but it works like a charm!

 

[Rick Rothstein]

City: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",255)),255,255))

Awesome. No idea what the City formula did, but it works like a charm!

There could be one possible problem with Jonmo1's City formula depending on what is allowed in the address part of your data. If suite or apartment numbers are allowed, they usually appear comma-separated from the address proper, something like this...

101 Main Street, Apt. 3B, Cherry Hill, NJ 08510

If your database allows that, then Jonmo1's formula will return "Apt. 3B" instead of "Cherry Hill". Here is a formula that should work with or without suite/apartment numbers...

=TRIM(LEFT(RIGHT(SUBSTITUTE(A2,",",REPT(" ",250)),500),250))

 

[zsunderland]

Welcome to the board

With the address in A2

Street: =LEFT(A2,FIND(",",A2)-1)
City: =TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",255)),255,255))
State: =MID(A2,LEN(A2)-7,2)
Zip: =RIGHT(A2,5)

You are a god, thank you