splitting address into cells

**johnmerlino** · 09-21-2010, 10:15 AM

Hey all,

I have this in one cell:

901 NE 8 ST HALLANDALE BEACH FL 33009-2626

Is there a way using a macro, some vbd code, that I can convert address, city, state, and zip to their own columns, where the zip may be 5 or 9 digits. I made an attachment.

Thanks for any response.

**arthurbr** · 09-21-2010, 10:21 AM

Hi
can you provide some more examples?
This way we can see how your strings are organized. Adding a sample sheet would help, noone likes to retype data - Thx

**MarvinP** · 09-21-2010, 10:53 AM

Hi John,

With your example data it is hard to come up with a rule to parse out the zip codes. If they are all FL addresses we could key on that.. IE anything after "FL" must be a zip.

The other problem is that some of the city names are two words. If they were all one word city names we could key on that to parse them out. A larger set of data would allow rules to be attempted to extract your data into an address table.

hope that helps.

**johnmerlino** · 09-21-2010, 11:14 AM

They will be either Florida or New York and only cities in Broward County. So I'm thinking of something like this:
stateArray = Split("FL, NY")
cityArray = Split("Fort Lauderdale","Sunrise","Oakland Park")
And then somehow make excel return VALUE! for cells that don't have a city or state containing the indexes of these array.
Can anyone help with this matter using VBD using macros.
Thanks.

**johnmerlino** · 09-21-2010, 06:44 PM

Hey all,

The script below is an attempt to split address, city, state, and zip code into separate columns. Right now each record is in a single column with no delimiters:

2701 NW 64TH TER*MARGATE FL 33063-1703
901 NE 8 ST*HALLANDALE BEACH FL 33009-2626
1840 DEWEY ST UNIT 305*HOLLYWOOD FL 33020
3049 NE 4 AVE*WILTON MANORS FL 33334-2047
650 NE 56 CT*OAKLAND PARK FL 33334-3528

In my Personal.xlsb module in VBAProject, the following script when executed returns an error message "Run-time error '5': Invalid procedure call or argument':

Sub SplitAddress()
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
Dim arrCity() As String, loopCity As Integer

 
intloop = 1
arrState = Split("FL, NY")
arrCity = Split("Fort Lauderdale, Sunrise, Oakland Park, Pompano Beach")

 
With ActiveSheet
 
    Set rng = Range(.Cells(1, 1), .Cells(1, 1).End(xlDown))
    
    For loopState = 0 To 1
    
        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

    For loopCity = 0 To 3
    
           rng.Replace What:=" " & arrCity(loopCity) & ". ", Replacement:=" " & arrCity(loopCity) & " "
            rng.Replace What:=" " & arrCity(loopCity) & " ", Replacement:=", " & arrCity(loopCity) & " "

        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

Thanks for any response.

**MarvinP** · 09-21-2010, 06:59 PM

Hi John,

The normal way to do these things is to key on the space between names. Pulling off the zip code is pretty easy. Then if they are only FL or NY that can be done. The problem comes with two word city names. Oakland Park is an example. One way the town is just Park because of the space. The other way 120 South Main Sunrise FL 32145 comes out with Main Sunrise as the city name.

If we could see the data and work with it there might be a method, but without some real samples it just doesn't fit into a good rule.

**Marcol** · 09-21-2010, 07:25 PM

Is this not for all intents and purposes the same quesrtion as this?

http://www.excelforum.com/excel-gene...nto-cells.html

**shg** · 09-21-2010, 07:29 PM

A lookup table of Florida cities would simplify things.

http://www.stateofflorida.com/Portal....aspx?tabid=34

EDIT: Threads merged.

**johnmerlino** · 09-21-2010, 07:42 PM

Originally Posted by shg

A lookup table of Florida cities would simplify things.

http://www.stateofflorida.com/Portal....aspx?tabid=34

EDIT: Threads merged.

Could you elaborate on what you mean by lookup table of florida cities? Thanks.

**johnmerlino** · 09-21-2010, 07:44 PM

Hey all, I figured out how to get the zip codes and state in their own column. Still stuck on placing city and address in its own column. There is a pattern though. The addresses will end with one of the following: TER, ST, AVE, CT, Blvd, Rd, Hwy, Exp, Pkwy.

Is there an excel formula that can check for this pattern: TER, ST, AVE, CT, BLVD, RD, HWY, EXP, PKWY.