CODEWITHSUNDEEP
excelvbaexcel-formula
Kevin Tuleta
Kevin Tuleta

Reputation: 9

Parse "City, State" and "Zip" from text formatted as "City, State Zip"

I need to consolidate the "City, State" from the "Zip" in a list of 4,000+ store addresses (small sample below).

What do I need to do in order to split out ONLY the cells that have "City, State Zip" into two new columns that have "City, State" in one, and "Zip" in the other, while ignoring all the other cells?

Bel Air     
3436 Bel Air Mall       
Mobile, AL 36606
Bridge Street       
330 The Bridge Street       
Huntsville, AL 35806        
Colonial Mall Auburn        
1627 Opelika Road       
Auburn, AL 36830        
Eastchase       
6850 Eastchase Parkway      
Montgomery, AL 36117        
Eastern Shore Centre        
30500 Highway 181       
Spanish Fort, AL 36527      
Gadsden     
1001 Rainbow Drive      
Gadsden, AL 35901

Upvotes: 0

Views: 383

Answers (1)

Josh Pachner
Josh Pachner

Reputation: 521

Here some working code. Assuming that your addresses are in an excel sheet column "A". And that they all follow the same format as your examples

Sub split_out_zip()

For x = 1 To Range("A" & Rows.Count).End(xlUp).Row
Line = trim(Cells(x, "A"))

If InStr(Line, ",") Then
    zip = Right(Line, 5)
    cityState = Left(Line, Len(Line) - 5)
    Cells(x, "B") = cityState
    Cells(x, "C") = zip

End If

Next x
End Sub

This will output it in column B and C

Upvotes: 1

Related Questions