Excel split cells with full name into 2 columns.

[Golaidron]

Hi All

Need help her to find some formulas til split up a cell and into 2 cells.
Got thousands of row to edit.

Data i got in column A is set up randomly,

Lastname, Firstname
Lastname, Firstname Middelname
Lastname, Firstname Middelname Middelname

Firstname Lastname
Firstname Middelname Lastname
Firstname Middelname Middelname Lastname

Preferred outcome is
Column B to contain First+middelname
Column C to contain only lastname

Please help.

 

[tyija1995]

Hi Golaidron,

EXTRACTELEMENT and COUNTSPACES are UDFs - made in the module of the workbook you want to use them in.

You'll need to open up the VBA and paste the function codes in to setup the functions ready to use.

Hold Alt and press F11 to open up VBA, then go to Insert -> Module
Paste the code into the editor and close the VBA (Alt+F11)
Now try the formula again and it should work.

Paste this:

Function EXTRACTELEMENT(txt, n, sep)
    EXTRACTELEMENT = Split(Application.Trim(txt), sep)(n - 1)
End Function


Function COUNTSPACES(txt As String) As String
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = "\S"
        COUNTSPACES = Len(.Replace(txt, ""))
    End With
End Function

 

[Golaidron]

Thanks tyija1995.

Everything is perfect now.

 

[jtakw]

Hi,

No need to install UDFs, these formulas should work:

Book1
	A	B	C
1	Jamesson, Adam Mark Terry	Adam Mark Terry	Jamesson
2	Peter Richard Clark	Peter Richard	Clark
3	Thatcher, Liz Amy	Liz Amy	Thatcher
Sheet615

Cell Formulas
Range	Formula
B1	=TRIM(SUBSTITUTE(A1,IFERROR(LEFT(A1,FIND(",",A1)),TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))),""))
C1	=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1,""),",",""))

Formulas copied down.