Split full name to first and middle and last name with single formula

[Prasad K]

Is any only single formula to split full name as first and middle and last name

 

[Rick Rothstein]

You do not need a looping macro for the functionality you asked for. Assuming cell A1 is a header cell...

Sub Macro2()
  Range("A2:A" & Rows.Count).TextToColumns Range("B2"), xlDelimited, , , False, False, False, True, False
End Sub

If cell A1 is a data cell also, then you can use this instead...

Sub Macro2()
  Columns("A").TextToColumns Range("B2"), xlDelimited, , , False, False, False, True, False
End Sub

Note: This macro as well as the one you have marked as the Answer do not account for two-name last names like "Della Rosa" (I went to school with someone with that last name), "da Vinci", "von Newman", and such.

 

[maabadi]

Do you Change macro to This:

Sub Macro3()
Dim Lr As Long, i As Long, j As Long, arr As Variant
    Lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To Lr
     arr = Range("A" & i).Value
     Range("B" & i).Resize(, Len(arr) - Len(Replace(arr, ",", "")) + 1).Value = Split(arr, ",")
    Next i
 End Sub

 

[skf786]

Do you Change macro to This:

Sub Macro3()
Dim Lr As Long, i As Long, j As Long, arr As Variant
    Lr = Range("A" & Rows.Count).End(xlUp).Row
    For i = 2 To Lr
     arr = Range("A" & i).Value
     Range("B" & i).Resize(, Len(arr) - Len(Replace(arr, ",", "")) + 1).Value = Split(arr, ",")
    Next i
 End Sub

Many thanks, this works perfectly. Also would it be possible for the macro to put headers on the created columns i.e. "Add1" "Add2".......

 

[maabadi]

Try This:

Sub Macro3()
Dim Lr As Long, i As Long, j As Long, arr As Variant
    Lr = Range("A" & Rows.Count).End(xlUp).Row
Range("B1").Resize(,3).Value = Array("Add1","Add2","Add3")
    For i = 2 To Lr
     arr = Range("A" & i).Value
     Range("B" & i).Resize(, Len(arr) - Len(Replace(arr, ",", "")) + 1).Value = Split(arr, ",")
    Next i
 End Sub

 

[skf786]

Try This:

Sub Macro3()
Dim Lr As Long, i As Long, j As Long, arr As Variant
    Lr = Range("A" & Rows.Count).End(xlUp).Row
Range("B1").Resize(,3).Value = Array("Add1","Add2","Add3")
    For i = 2 To Lr
     arr = Range("A" & i).Value
     Range("B" & i).Resize(, Len(arr) - Len(Replace(arr, ",", "")) + 1).Value = Split(arr, ",")
    Next i
 End Sub

thank you. this works nicely.

 

[Rick Rothstein]

Just wondering if you gave what I posted in Message #11 a try or not?