String Manipulation, get substring after space

[Deverti]

i have a repeating cell format i need to split into two parts, "Nr" and "Street, PLZ", format being: "Nr Street, PLZ"

im getting the "Nr" as needed but the "Street, PLZ" is a total screw up and im not quite sure what im doing wrong

Sub StringTitleRelocate()
Dim vR As Range, vN As Variant, vT As Variant
    For Each vR In Range("C1", Range("C" & Rows.Count).End(xlUp))
        If Not IsEmpty(vR) Then
            vN = (Split(vR, " ")(0))
            vTi = (Split(vR, " ")(0))        '?'
            vT = Mid(vR, vTi)                '?'
            vR.Offset(1, 3).Value = vN
            vR.Offset(1, -2).Value = vT
        End If
        vR.ClearContents
    Next vR
End Sub

 

[Fluff]

How about

Sub StringTitleRelocate()
Dim vR As Range, vN As Variant, vT As Variant
    For Each vR In Range("C1", Range("C" & Rows.Count).End(xlUp))
        If Not IsEmpty(vR) Then
            vN = Split(vR, " ", 2)
            vR.Offset(1, 3).Value = vN(0)
            vR.Offset(1, -2).Value = vN(1)
        End If
        vR.ClearContents
    Next vR
End Sub

 

[Deverti]

Oh, guess i totally misunderstood the Limit option.
That s indeed a lot better. Thanks Fluff

 

[Fluff]

You're welcome & thanks for the feedback