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

[natemoss1218]

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!

 

[alansidman]

Can be achieved using Power Query also called Get and Transform Data and found on the Data Tab of the Ribbon

Book4
	A	B	C	D
1	Column1.1	Column1.2.2	Column1.2.3	Column1.2.4
2	1234 S Blackhawk Way	AURORA	CO	80015-
3	1234 S Monaco Street	DENVER	CO	80237
4	1234 E Girard Place	ENGLEWOOD	CO	80112
5	1234 W Burgundy Avenue APT 301	LITTLETON	CO	80127
6	1234 W 60th Street	GOLDEN	CO	80403
7	1234 E Mexico Avenue APT Unit 403	DENVER	CO	80222
8	1234 S Spotswood Street	LITTLETON	CO	80120
9	1234 E Euclid Drive	AURORA	CO	80016
Table1

Mcode for this is as follows.

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]

This will get you the city...

=TRIM(LEFT(RIGHT(SUBSTITUTE(SUBSTITUTE(A1," ",", ",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1),",",REPT(" ",300)),600),300))

This will get you the zip code...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",300)),300))

 

[johnnyL]

Assuming the Addresses are in column A, Adjust formulas accordingly if not. These are the formulas I came up with:

For City

=REPLACE(LEFT(A1,LEN(A1)-9),1,FIND(",",A1)+1,"")

For Zip 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]

Here is the data I used:

Book1
	A	B	C	D
1	1234 S Blackhawk Way, AURORA CO 80015-	AURORA	80015
2	123 S Blackhawk Way, AURORA CO 80015	AURORA	80015
3	12 S Monaco Street, DENVER CO 80237	DENVER	80237
4	4 E Girard Place, ENGLEWOOD CO 80112	ENGLEWOOD	80112
5	1234 W Burgundy Avenue APT 301, LITTLETON CO 80127	LITTLETON	80127
6	1234 W 60th Street, GOLDEN CO 80403	GOLDEN	80403
7	1234 E Mexico Avenue APT Unit 403, DENVER CO 80222	DENVER	80222
8	1234 S Spotswood Street, LITTLETON CO 80120	LITTLETON	80120
9	1234 E Euclid Drive, AURORA CO 80016	AURORA	80016
10
Sheet1

Cell Formulas
Range	Formula
B1:B9	B1	=REPLACE(LEFT(A1,LEN(A1)-9),1,FIND(",",A1)+1,"")
C1:C9	C1	=LEFT(TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),LEN(A1))),5)

 

[Rick Rothstein]

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]

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]

Thank you everyone for all the answers this has been extremely helpful!!