Extract city and zip from list of addresses (Street Address, City State Zip)

natemoss1218

New Member
Joined
Aug 6, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
This question is very similar to a few others, but differs just enough that it warranted its own thread.

I have a list of addresses like this:
1234 S Blackhawk Way, AURORA CO 80015-
1234 S Monaco Street, DENVER CO 80237
1234 E Girard Place, ENGLEWOOD CO 80112
1234 W Burgundy Avenue APT 301, LITTLETON CO 80127
1234 W 60th Street, GOLDEN CO 80403
1234 E Mexico Avenue APT Unit 403, DENVER CO 80222
1234 S Spotswood Street, LITTLETON CO 80120
1234 E Euclid Drive, AURORA CO 80016

Because they are missing the second comma after the city, I am having a tough time extracting the city. Any help getting the city to one column as well as the zip code would be a massive help! I can do it in power query, just trying to figure out the Excel formula for it.

Thanks for the help!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,576
Office Version
  1. 365
Platform
  1. Windows
Can be achieved using Power Query also called Get and Transform Data and found on the Data Tab of the Ribbon
Book4
ABCD
1Column1.1Column1.2.2Column1.2.3Column1.2.4
21234 S Blackhawk WayAURORACO80015-
31234 S Monaco StreetDENVERCO80237
41234 E Girard PlaceENGLEWOODCO80112
51234 W Burgundy Avenue APT 301LITTLETONCO80127
61234 W 60th StreetGOLDENCO80403
71234 E Mexico Avenue APT Unit 403DENVERCO80222
81234 S Spotswood StreetLITTLETONCO80120
91234 E Euclid DriveAURORACO80016
Table1


Mcode for this is as follows.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({","}, QuoteStyle.Csv, true), {"Column1.1", "Column1.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Column1.2.1", "Column1.2.2", "Column1.2.3", "Column1.2.4"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Column1.2.1"})
in
    #"Removed Columns"

Explanation of Steps"

On the Data Tab, Get and Transform--From Range/Table
In the PQ Editor, on the Home Tab, Select Split Column, Select Comma and Right only
In the PQ Editor, highlight the new column and Select Split Column and Select Space -- All
On the Home tab, Select Close and Load to; Make your selections.
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
This will get you the city...
Excel Formula:
=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," ",", ",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),",",REPT(" ",300)),600),300))
This will get you the zip code...
Excel Formula:
=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",300)),300))
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,490
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Assuming the Addresses are in column A, Adjust formulas accordingly if not. These are the formulas I came up with:

For City
VBA Code:
=REPLACE(LEFT(A1,LEN(A1)-9),1,FIND(",",A1)+1,"")

For Zip code
VBA Code:
=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),5)

BTW @Rick Rothstein your formula doesn't work for the first example with the dash on the end of the string, your formula leaves the dash along with the 5 digits. ;)
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,490
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

Here is the data I used:

Book1
ABCD
11234 S Blackhawk Way, AURORA CO 80015-AURORA 80015
2123 S Blackhawk Way, AURORA CO 80015AURORA80015
312 S Monaco Street, DENVER CO 80237DENVER80237
44 E Girard Place, ENGLEWOOD CO 80112ENGLEWOOD80112
51234 W Burgundy Avenue APT 301, LITTLETON CO 80127LITTLETON80127
61234 W 60th Street, GOLDEN CO 80403GOLDEN80403
71234 E Mexico Avenue APT Unit 403, DENVER CO 80222DENVER80222
81234 S Spotswood Street, LITTLETON CO 80120LITTLETON80120
91234 E Euclid Drive, AURORA CO 80016AURORA80016
10
Sheet1
Cell Formulas
RangeFormula
B1:B9B1=REPLACE(LEFT(A1,LEN(A1)-9),1,FIND(",",A1)+1,"")
C1:C9C1=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),5)
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,484
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I wasn't sure what to do about that dash. Using Left(--,5) or SUBSTITUTE'ing "" for the dash means Zip Codes in #####-#### format would be handled incorrectly. So, I figured I would post what I did and see what the OP said.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,862
Office Version
  1. 365
Platform
  1. Windows
Since you have Excel 365 you can try FLASH FILL.
For the city type in the first few cities so Excel can figure out the pattern then go to DATA and FLASH FILL.
Do the same for your Zip codes.

 

natemoss1218

New Member
Joined
Aug 6, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Thank you everyone for all the answers this has been extremely helpful!!
 

Forum statistics

Threads
1,144,394
Messages
5,724,087
Members
422,536
Latest member
Zeeshan53

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