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!

2106 pacific ave Virginia Beach VA 23451

2106 pacific ave Virginia Beach VA 23451

625 Spooner Rd Virginia Beach VA 23462

764 S Military Hwy # B Virginia Beach VA 23464

764 S Military Hwy # B Virginia Beach VA 23464

Virginia Beach VA 23462

Virginia Beach VA 23462

620 Baker Rd Virginia Beach VA 23462

1109 Gladiola Crescent Virginia Beach VA 23453

1109 Gladiola Crescent Virginia Beach VA 23453

1109 Gladiola Crescent Virginia Beach VA 23453

369 Independence Blvd Virginia Beach VA 23462

Virginia Beach VA

3590 Holland Rd, Ste 100 Virginia Beach VA 23453

101 N Lynnhaven Rd. Ste202 Virginia Beach VA 23452

3877 Holland Rd, Ste 424 Virginia Beach VA 23452

3877 Holland Rd, Ste 424 Virginia Beach VA 23452

516 S Military Hwy # B Virginia Beach VA 23464

4107 Thistle Cir Virginia Beach VA 23462-4934

4107 Thistle Cir Virginia Beach VA 23462-4934

508 N Birdneck Rd Ste G Virginia Beach VA 23451

508 N Birdneck Rd Ste G Virginia Beach VA 23451

4554 Virginia Beach Blvd Virginia Beach VA 23462

649 Newtown Rd Virginia Beach VA 23462

Virginia Beach VA 23462

Virginia Beach VA 23462

Virginia Beach VA 23462

6049 Indian River Rd Virginia Beach VA 23464

3061 Brickhouse Court, Suite 109 Virginia Beach VA 23452

5020 Ferrell Pkwy Num 205-163 Virginia Bch VA 23464

5020 Ferrell Pkwy Num 205-163 Virginia Bch VA 23464

701 Lynnhaven Pkwy Virginia Beach VA 23452

701 Lynnhaven Pkwy Virginia Beach VA 23452

1709 Schooner Strait Ct Virginia Beach VA 23453

1121 Clydesdale Ln Virginia Beach VA 23464

Bank St Norfolk VA 11111

Virginia Beach VA 23462

Virginia Beach VA 23462

108 Smythe Ct Apt 102 Virginia Beach VA 23452-1721

108 Smythe Ct Apt 102 Virginia Beach VA 23452-1721

3030 Virginia Beach Blvd Virginia Beach VA 23452

1777 London Bridge Rd Virginia Beach VA 23453

3112 Virginia Beach Blvd Virginia Beach VA

1 Columbus Ctr # 700 Virginia Beach VA 23462

3615 Atlantic Ave Virginia Beach VA 23451

3565 Purebred Dr Virginia Beach VA 23453

Virginia Beach VA 23462

Virginia Beach VA 23462

Virginia Beach VA 23462

1146 Rodgers St Chesapeake VA 23324

VA 23452

4241 Bonney rd Virginia Beach VA 23452

4241 Bonney rd Virginia Beach VA 23452

210 S. Witchduck Rd Virginia Beach VA 23464

210 S. Witchduck Rd Virginia Beach VA 23464

3778 Virginia Beach Blvd Virginia Beach VA 23452

3778 Virginia Beach Blvd Virginia Beach VA 23452

4988 Euclid Rd Virginia Beach VA 23462

<colgroup><col></colgroup><tbody>
</tbody>

 

[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.