Avatar of Gary11734
Gary11734Flag for United States of America asked on

EXCEL VBA Parsing City State ZIP

I have an Excel File I want to manipulate. One column  includes City, State and Zip in the same cell.
I would like a macro that will loop through the following:

Portion of the file:
COLORADO SPRINGS, CO 80901-2810
JACKSONVILLE, FL 32206-0005
JACKSONVILLE, FL 32226
JACKSONVILLE, FL 32256-1308
GAINESVILLE, FL 32606
PONTE VEDRA BEACH, FL 32082
OSKALOOSA, IA 52577
JACKSONVILLE, FL 32256
JACKSONVILLE, FL 32250
ST. AUGUSTINE, FL 32084

Since I need to parse this data that has many variables, I thought the best way would be the following:

Loop through the column  and move the states first. Then another  pass would be to either move the remaining text or Number (Zip Code). The move would consist of moving the data either right or left to the adjacent row.

Thanks for the help

Gary
 
VB ScriptMicrosoft Excel

Avatar of undefined
Last Comment
Gary11734

8/22/2022 - Mon
jppinto

Does your data have always the same format? Does it always have the City, State and Zip in all the rows?

João Pinto
nutsch

you could do it with formulas. Three columns to retrieve your towns. Copy down to cover all rows.

State:=mid(A1,find(",",A1)+2,2)
Zip=RIGHT(A1,LEN(A1)-FIND(",",A1)-4)
Town=LEFT(A1,FIND(",",A1)-1)
nutsch

Macro version:

Sub SplitAddress()
Dim intloop As Integer, intFindComma As Integer
Dim strCity As String, strZip As String, strState As String
Dim strAdd As String
intloop = 1

With ActiveSheet

    Do While .Cells(intloop, 1).Value <> ""
        strAdd = .Cells(intloop, 1).Value
        intFindComma = InStr(strAdd, ",")
        .Cells(intloop, 2).Value = Mid(strAdd, intFindComma + 2, 2)
        .Cells(intloop, 3).Value = Right(strAdd, Len(strAdd) - intFindComma - 4)
        .Cells(intloop, 4).Value = Left(strAdd, intFindComma - 1)

        intloop = intloop + 1
    Loop

End With
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
macksm

use split comand split the data at commas into two
rput relevant values sourcesheet and destination sheets

private sub parsemydata()
dim tempsplit() as String
dim sourcesheet as worksheet
dim destination as worksheet
dim tempsplit2() as string
dim i as integer
i=1
for i=1 to totrecords 
 
tempsplit=split(sourcesheet.range("a"&i),',')
destination t.range("A"& i)=tempsplit(0)
tempsplit2=split(tempsplit(1),' ')
destination.range("b"&i)=tempsplit2(0)
destination.range("c" &i )=tempsplit2(1)
next 
end sub

Open in new window

ASKER
Gary11734

Hi nutsch

Thanks for the quick response

The city state Zip is the same but I missed pointing out that some Cells do not have commas after the city.
Sample:
ORANGE PARK, FL 32073
ORANGE PARK, FL 32073
JACKSONVILLE, FL 32211
JACKSONVILLE FL  32256
DEERFIELD BEACH, FL 33442
WILLIAMSBURG VA 23188
JACKSONVILLE, FL 32207
JACKSONVILLE FL 32258
ORANGE PARK FL 32073
Jacksonville FL 32205
ST JOHN, FL. 32259
JACKSONVILLE, FL 32258
nutsch

Little change in the code, it now cleans up your states, by removing the trailing dot if needed and adding the comma first before running.
Sub SplitAddress2()
Dim intloop As Integer, intFindComma As Integer
Dim strCity As String, strZip As String, strState As String
Dim strAdd As String, cl As range, rng As range
Dim arrState() As String, loopState As Integer
 
intloop = 1
arrState = Split("AL,AK,AZ,AR,CA,CO,CT,DE,DC,FL,GA,HI,ID,IL,IN,IA,KS,KY,LA,ME,MD,MA,MI,MN,MS,MO,MT,NE,NV,NH,NJ,NM,NY,NC,ND,OH,OK,OR,PA,RI,SC,SD,TN,TX,UT,VT,VA,WA,WV,WI,WY", ",")
 
With ActiveSheet
 
    Set rng = range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
    
    For loopState = 0 To 49
    
        rng.Replace What:=" " & arrState(loopState) & ". ", Replacement:=" " & arrState(loopState) & " "
        rng.Replace What:=" " & arrState(loopState) & " ", Replacement:=", " & arrState(loopState) & " "
    
    Next
    
    rng.Replace What:=",,", Replacement:=","
    
    Do While .Cells(intloop, 1).Value <> ""
        strAdd = .Cells(intloop, 1).Value
        intFindComma = InStr(strAdd, ",")
        .Cells(intloop, 2).Value = Mid(strAdd, intFindComma + 2, 2)
        .Cells(intloop, 3).Value = Right(strAdd, Len(strAdd) - intFindComma - 4)
        .Cells(intloop, 4).Value = Left(strAdd, intFindComma - 1)
 
        intloop = intloop + 1
    Loop
    
End With
End Sub

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
nutsch

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
Gary11734

nutsch,

The second one worked great ---Sub SplitAddress2()
I am uploading my file with your code if anybody wants to run it to see it work.

Thanks Again

Gary
611Upload.xls