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

 

[Prasad K]

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

ignore this question i have get answer
=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",LEN($A1))),COLUMNS($A:C)*LEN($A1)-(LEN($A1)-1),LEN($A1)))

 

[Snakehips]

Just because I had worked on it. But I may have interpreted the question wrongly?

TEST_MREXCEL.xlsm
	A	B	C	D	E	F
2	Frederick Stanley Smith	Frederick	Stanley	Smith
3	Frederick Stanley Albert Reginald Smith-Lewington	Frederick	Stanley	Albert	Reginald	Smith-Lewington
4
Sheet2

Cell Formulas
Range	Formula
B2:F3	B2	=TRIM(MID(SUBSTITUTE(TRIM($A2)," ",REPT(" ",100)),((COLUMNS($B:B)-1)*100)+1,100))

 

[Prasad K]

Just because I had worked on it. But I may have interpreted the question wrongly?

TEST_MREXCEL.xlsm
	A	B	C	D	E	F
2	Frederick Stanley Smith	Frederick	Stanley	Smith
3	Frederick Stanley Albert Reginald Smith-Lewington	Frederick	Stanley	Albert	Reginald	Smith-Lewington
4
Sheet2

Cell Formulas
Range	Formula
B2:F3	B2	=TRIM(MID(SUBSTITUTE(TRIM($A2)," ",REPT(" ",100)),((COLUMNS($B:B)-1)*100)+1,100))

one more small help

i have recorded this below macro for split full name as first and middle and last it's working perfectly to me & i want change this macro to

When i run this macro to split names it never show the formula in cell

Sub SplitNames()
'
' SplitNames Macro
'

'
    ActiveSheet.Paste
    ActiveCell.FormulaR1C1 = _
        "=TRIM(MID(SUBSTITUTE(RC1,"" "",REPT("" "",LEN(RC1))),COLUMNS(C1:C[-1])*LEN(RC1)-(LEN(RC1)-1),LEN(RC1)))"
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A4")
    ActiveCell.Range("A1:A4").Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:C4"), Type:= _
        xlFillDefault
    ActiveCell.Range("A1:C4").Select
    ActiveCell.Offset(4, 0).Range("A1").Select
End Sub

 

[maabadi]

if you want macro try this:

Sub Macro2()
Dim Lr As Long, i As Long, j As Long
    Lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("B1").FormulaR1C1 = _
        "=TRIM(MID(SUBSTITUTE(TRIM(RC1),"" "",REPT("" "",100)),((COLUMNS(C2:C)-1)*100)+1,100))"
    Range("B1").AutoFill Destination:=Range("B1:B" & Lr)
    Range("B1:B" & Lr).AutoFill Destination:=Range("B1:H" & Lr)
    Range("B1:H" & Lr).Value = Range("B1:H" & Lr).Value
End Sub

But also you can do it with Text to column option at Data Tab with Space as Delimiter

 

[maabadi]

Or try this simpler Macro:

Sub Macro2()
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

 

[Prasad K]

if you want macro try this:

Sub Macro2()
Dim Lr As Long, i As Long, j As Long
    Lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("B1").FormulaR1C1 = _
        "=TRIM(MID(SUBSTITUTE(TRIM(RC1),"" "",REPT("" "",100)),((COLUMNS(C2:C)-1)*100)+1,100))"
    Range("B1").AutoFill Destination:=Range("B1:B" & Lr)
    Range("B1:B" & Lr).AutoFill Destination:=Range("B1:H" & Lr)
    Range("B1:H" & Lr).Value = Range("B1:H" & Lr).Value
End Sub

But also you can do it with Text to column option at Data Tab with Space as Delimiter

Thank you so much it's working has per my request

 

[Prasad K]

Or try this simpler Macro:

Sub Macro2()
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

This one also working perfectly Once again Thank you so much

 

[maabadi]

You're Welcome & Thanks for Feedback.

 

[skf786]

Or try this simpler Macro:

Sub Macro2()
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

[/CODE
[/QUOTE]

Or try this simpler Macro:

Sub Macro2()
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

using the same logic/macro on a field containing address which is to be split based on commas instead of spaces, can we replace " " with ","? doesnt seem to work. sample text is given below:

4668 Stonecoal Road,Celina, ohio,45822