Extract City and State from address

scojax22

New Member
Joined
Apr 1, 2014
Messages
4
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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)
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,679
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
May 11, 2018
Messages
1
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top