Split varied addresses into columns

[realtoast]

Experts,

I customer provided table that has a single field, Address, where Address 1, Address 2, City, State, Zip are separated by comma. I need a formula or sequence of steps to split these addresses into columns titled, Address 1, Address 2, City, State, Zip. As you can see, because some addresses have apartment numbers or other text after the first comma, Text To Columns shuffles the results. I figure, if I can get City, State, Zip extracted to a column, I can use Text To Columns to separate them properly. But what to do with the left-most data, where some have apartments and others don't?

Here is an example of the table (fake addresses):

4233 JOSSELYN CANYON RD, 21, MONTEREY, CA, 93940

4233 JOSSELYN CANYON RD, 21, MONTEREY, CA, 93940

4233 JOSSELYN CANYON RD, 21, MONTEREY, CA, 93940

4233 JOSSELYN CANYON RD, 21, MONTEREY, CA, 93940

4233 JOSSELYN CANYON RD, 21, MONTEREY, CA, 93940

4233 JOSSELYN CANYON RD, 21, MONTEREY, CA, 93940

8735 YANKEE JIM CT., VALLEJO, CA, 94589

8163 W. MONTE VISTA AVE, #261, TURLOCK, CA, 95351

1578 DANIELS AVE, APT #45, VALLEJO, CA, 94590

975 MOSSWOOD LANE, SANTA ROSA, CA, 95401

74789 CEDAR RD, VISTA, CA, 92083

7534 CHRYSLER DR, APT #1, MODESTO, CA, 95350

7625 EAST LINWOOD AVE, 78, TURLOCK, CA, 95380

9987 EUREKA DRIVE, TURLOCK, CA, 95380

9987 EUREKA DRIVE, TURLOCK, CA, 95380

8976 N BERKELEY AVENUE, TURLOCK, CA, 95382

9823 MELINDA AVENUE , DELHI, CA, 95315

9823 MELINDA AVENUE , DELHI, CA, 95315

9823 MELINDA AVENUE , DELHI, CA, 95315

9823 MELINDA AVENUE , DELHI, CA, 95315

9823 MELINDA AVENUE , DELHI, CA, 95315

2809 VIA PIEDMONT, GUSTINE, CA, 95322

1321 NUNES ROAD, APT 27, TURLOCK, CA, 95382

1321 NUNES ROAD, APT 27, TURLOCK, CA, 95382

8965 SILVERADO DRIVE, MANTECA, CA, 95337

8965 SILVERADO DRIVE, MANTECA, CA, 95337

8965 SILVERADO DRIVE, MANTECA, CA, 95337

8965 SILVERADO DRIVE, MANTECA, CA, 95337

8965 SILVERADO DRIVE, MANTECA, CA, 95337

8965 SILVERADO DRIVE, MANTECA, CA, 95337

 

[Fuji]

We need to stop using Regular Expressions, so

Sub test()
    Dim a, i&, x
    a = [a1].CurrentRegion.Value
    For i = 1 To UBound(a, 1)
        x = Split(a(i, 1), ",")
        If UBound(x) < 4 Then x(0) = x(0) & ","
        a(i, 1) = Join(x, ",")
    Next
    With [c1].Resize(UBound(a, 1))
        .CurrentRegion.ClearContents
        .Value = a
        .TextToColumns .Cells(1), 1, comma:=True
    End With
End Sub

 

[Peter_SSs]

If the above achieves the wanted results then a slightly different approach to consider might also be

Sub Test2()
  Dim a, i&
  a = [a1].CurrentRegion.Value

  a = Range("A1").CurrentRegion.Value
  For i = 1 To UBound(a, 1)
    a(i, 1) = Replace(a(i, 1), ",", ",,", , -(Len(a(i, 1)) - Len(Replace(a(i, 1), ",", "")) < 4))
  Next i
  With [c1].Resize(UBound(a, 1))
      .Value = a
      .TextToColumns , 1, comma:=True, other:=False
      .Resize(, 4).Columns.AutoFit
  End With
End Sub

 

[Fuji]

No loop,

Sub test()
    With Range("a1", Range("a" & Rows.Count).End(xlUp)).Columns(3)
        .Columns("a:e").ClearContents
        .Value = Evaluate(Replace("if(len(#)-len(substitute(#,"","",""""))<4,substitute(#,"","","",,"",1),if(#<>"""",#,""""))", "#", .Columns(-1).Address))
        .TextToColumns .Cells(1), 1, comma:=True
        .CurrentRegion.Columns.AutoFit
    End With
End Sub

 

[realtoast]

We need to stop using Regular Expressions, so

Sub test()
    Dim a, i&, x
    a = [a1].CurrentRegion.Value
    For i = 1 To UBound(a, 1)
        x = Split(a(i, 1), ",")
        If UBound(x) < 4 Then x(0) = x(0) & ","
        a(i, 1) = Join(x, ",")
    Next
    With [c1].Resize(UBound(a, 1))
        .CurrentRegion.ClearContents
        .Value = a
        .TextToColumns .Cells(1), 1, comma:=True
    End With
End Sub

This works! Much easier than the sequences of various tasks I was trying. Thank you!

 

[realtoast]

No loop,

Sub test()
    With Range("a1", Range("a" & Rows.Count).End(xlUp)).Columns(3)
        .Columns("a:e").ClearContents
        .Value = Evaluate(Replace("if(len(#)-len(substitute(#,"","",""""))<4,substitute(#,"","","",,"",1),if(#<>"""",#,""""))", "#", .Columns(-1).Address))
        .TextToColumns .Cells(1), 1, comma:=True
        .CurrentRegion.Columns.AutoFit
    End With
End Sub

This works! Thank you so much. Much simpler than my efforts.