Sub splitAddress()

Dim fullAddress, city, address, zipCode As String
Dim stringLength, spaceLoc, thirdCommaLoc As Integer
Dim currRow As Integer
Dim varString As String
Dim i As Integer
Dim j As Integer
Dim intCommaPosition As Variant


For currRow = 1 To 500
    If Len(Trim(ActiveSheet.Cells(currRow, 3).Value)) <> 0 Then
        fullAddress = ActiveSheet.Cells(currRow, 3)
        varString = fullAddress
        intCommaPosition = 0
    For i = 1 To 3
        j = InStr(1, varString, ",")
        If j = 0 Then
            Exit For
        Else
            varString = Right(varString, Len(varString) - j)
        End If
        intCommaPosition = intCommaPosition + j
    Next i

        stringLength = Len(fullAddress)
        spaceLoc = InStr(1, fullAddress, " ")
        thirdCommaLoc = intCommaPosition
        ActiveSheet.Cells(currRow, 6) = Left(fullAddress, spaceLoc - 1)
        ActiveSheet.Cells(currRow, 7) = Mid(fullAddress, spaceLoc + 1, thirdCommaLoc - spaceLoc - 1)
        ActiveSheet.Cells(currRow, 8) = Right(fullAddress, sringLength - thirdCommaLoc - 1)
    End If
Next currRow

End Sub