VBA Parsing Street Addresses

=left(a1,find(" ",a1)-1)

=right(a1,len(a1)-len(b1)-1)

Sub Test()
    Dim WF As WorksheetFunction
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim Sp1 As Integer
    Dim Sp2 As Integer
    Set WF = WorksheetFunction
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:A" & Sh.Range("A65536").End(xlUp).Row)
    For Each Cell In Rng
        Sp1 = InStr(1, Cell.Value, " ")
        If Len(Cell.Value) - Len(WF.Substitute(Cell.Value, " ", "")) > 2 Then
            Sp2 = InStr(1, WF.Substitute(Cell.Value, " ", "@", 3), "@")
        Else
            Sp2 = InStr(1, WF.Substitute(Cell.Value, " ", "@", 2), "@")
        End If
        Cell.Offset(0, 1).Value = Left(Cell.Value, Sp1 - 1)
        Cell.Offset(0, 2).Value = Mid(Cell.Value, Sp1 + 1, Sp2 - Sp1 - 1)
        Cell.Offset(0, 3).Value = Right(Cell.Value, Len(Cell.Value) - Sp2)
    Next Cell
End Sub

Sub Parse()
'parses strings
'uses blank left to right to seperate address
Dim strtest As String, LastRow As Integer, cnt as Integer
Dim Temp As Integer, i As Integer, colcnt as Integer

LastRow = Sheets("sheet1").Cells(Sheets("sheet1") _
.Cells.Rows.Count, "A").End(xlUp).Row()

For cnt = 1 To LastRow
strtest = Sheets("sheet1").Cells(cnt, "A")

For colcnt = 3 To 4
For i = 1 To Len(strtest)
  Temp = Asc(Mid(strtest, i, 1))
  If Temp = 32 Then
  Sheets("sheet1").Cells(cnt, colcnt) = Left(strtest, i - 1)
  strtest = Right(strtest, Len(strtest) - i)
  Exit For
  End If
 Next i
Next colcnt

Sheets("sheet1").Cells(cnt, colcnt) = strtest
Next cnt
End Sub