# Extract City and State from address

#### scojax22

##### New Member
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Welcome to the board

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)

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

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))

Welcome to the board

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

Replies
5
Views
808
Replies
0
Views
555
Replies
5
Views
811
Replies
4
Views
1K
Replies
0
Views
216

1,219,939
Messages
6,151,077
Members
451,006
Latest member
dhinze84

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back