Separating Data Within Cells When it Contains Both Commas and Spaces

[tocharge]

Hi Guys, I'm having a hard time with this issue! I have a data set that contains addresses with delimiters that are sometimes commas and other times spaces. I want to separate the data out to "street address", "city", "state", and "zip code". Sometimes the full address is listed, but other times the user only supplied partial information like the city and state - or just the state and zip. Here's a short example of what I'm working with. Any help you can provide on getting this information into the appropriate columns would be greatly appreciated!

[TABLE="width: 513"]
<colgroup><col></colgroup><tbody>[TR]
[TD]2106 pacific ave Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]2106 pacific ave Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]625 Spooner Rd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]764 S Military Hwy # B Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]764 S Military Hwy # B Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]620 Baker Rd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]1109 Gladiola Crescent Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]1109 Gladiola Crescent Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]1109 Gladiola Crescent Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]369 Independence Blvd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA[/TD]
[/TR]
[TR]
[TD]3590 Holland Rd, Ste 100 Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]101 N Lynnhaven Rd. Ste202 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]3877 Holland Rd, Ste 424 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]3877 Holland Rd, Ste 424 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]516 S Military Hwy # B Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]4107 Thistle Cir Virginia Beach VA 23462-4934[/TD]
[/TR]
[TR]
[TD]4107 Thistle Cir Virginia Beach VA 23462-4934[/TD]
[/TR]
[TR]
[TD]508 N Birdneck Rd Ste G Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]508 N Birdneck Rd Ste G Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]4554 Virginia Beach Blvd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]649 Newtown Rd Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]6049 Indian River Rd Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]3061 Brickhouse Court, Suite 109 Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]5020 Ferrell Pkwy Num 205-163 Virginia Bch VA 23464[/TD]
[/TR]
[TR]
[TD]5020 Ferrell Pkwy Num 205-163 Virginia Bch VA 23464[/TD]
[/TR]
[TR]
[TD]701 Lynnhaven Pkwy Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]701 Lynnhaven Pkwy Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]1709 Schooner Strait Ct Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]1121 Clydesdale Ln Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]Bank St Norfolk VA 11111[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]108 Smythe Ct Apt 102 Virginia Beach VA 23452-1721[/TD]
[/TR]
[TR]
[TD]108 Smythe Ct Apt 102 Virginia Beach VA 23452-1721[/TD]
[/TR]
[TR]
[TD]3030 Virginia Beach Blvd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]1777 London Bridge Rd Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]3112 Virginia Beach Blvd Virginia Beach VA[/TD]
[/TR]
[TR]
[TD]1 Columbus Ctr # 700 Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]3615 Atlantic Ave Virginia Beach VA 23451[/TD]
[/TR]
[TR]
[TD]3565 Purebred Dr Virginia Beach VA 23453[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]Virginia Beach VA 23462[/TD]
[/TR]
[TR]
[TD]1146 Rodgers St Chesapeake VA 23324[/TD]
[/TR]
[TR]
[TD]VA 23452[/TD]
[/TR]
[TR]
[TD]4241 Bonney rd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]4241 Bonney rd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]210 S. Witchduck Rd Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]210 S. Witchduck Rd Virginia Beach VA 23464[/TD]
[/TR]
[TR]
[TD]3778 Virginia Beach Blvd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]3778 Virginia Beach Blvd Virginia Beach VA 23452[/TD]
[/TR]
[TR]
[TD]4988 Euclid Rd Virginia Beach VA 23462[/TD]
[/TR]
</tbody>[/TABLE]

 

[JLGWhiz]

This will get most of it done.

Sub t()
Dim c As Range, spl As Variant, i As Long
Columns("A:D").Insert
With ActiveSheet
    For Each c In .Range("E1", .Cells(Rows.Count, 5).End(xlUp))
        If Len(c) > 24 Then
            c.Offset(, -4) = Left(c.Value, Len(c.Value) - 24)
        End If
        If Len(c) > 22 Then
            c.Offset(, -3) = Mid(c.Value, Len(c.Value) - 22, 14)
        End If
        spl = Split(Mid(c.Value, InStrRev(c.Value, "VA")), " ")
        
        c.Offset(, -2) = spl(0)
        If UBound(spl) > 0 Then
            c.Offset(, -1) = spl(1)
        End If
        
    Next
    .Columns("A:D").AutoFit
End With
End Sub

 

[Joe4]

Note that whatever solution you come up with, it will not work 100% of the time. It is virtually impossible to come up with anything that is.
The reason is because it is impossible to come up with any sort of rule that tells it exactly where Street Name ends and City begins, as both could have multiple words.
So there is no "one size fits all" rule that will work all the time.

So, each time you use any sort of solution, you will probably need to scan your results any make corrections.
If you can come up with a solution that works on 90-95% of the entries, I would say that is pretty good.

 

[tocharge]

That worked pretty well, but where would I modify the code so it modifies the data I have for 2,415 rows (right now it only modifies the first 85 or so)

 

[JLGWhiz]

That worked pretty well, but where would I modify the code so it modifies the data I have for 2,415 rows (right now it only modifies the first 85 or so)

That is the problem that Joe4 was referring to. The code I funished was based on a pattern that I could see in the data from the OP. If your other data is not of the same pattern, or has different value strings in the key places that are used in the code, then it won't work for the 2400 rows. About the only thing you could consistently extract would be the zip code. There is no sure way to determine where the street address, the city and state end and/or begin. The only other solution to automate would require a manual effort to go through and put delimiters in where they can be used in code to separate the blocks of strings and put them into columns. Then code could be written to do the actual separation and distribution.

If you decide to put in the delimiters, I suggest you use semicolon or vertical bar.