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.

 

[sandy666]

something like that?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1.1[/td][td=bgcolor:#70AD47]Column1.2[/td][td=bgcolor:#70AD47]Column1.3[/td][td=bgcolor:#70AD47]Lastname[/td][td=bgcolor:#70AD47]Names[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mike, John Andrew[/td][td][/td][td=bgcolor:#E2EFDA]Mike,[/td][td=bgcolor:#E2EFDA]John[/td][td=bgcolor:#E2EFDA]Andrew[/td][td=bgcolor:#E2EFDA]Mike,[/td][td=bgcolor:#E2EFDA]John Andrew[/td][/tr]

[tr=bgcolor:#FFFFFF][td]John Mike Andrew[/td][td][/td][td]John[/td][td]Mike[/td][td]Andrew[/td][td]Andrew[/td][td]John Mike[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Olsen, Bjern Vito[/td][td][/td][td=bgcolor:#E2EFDA]Olsen,[/td][td=bgcolor:#E2EFDA]Bjern[/td][td=bgcolor:#E2EFDA]Vito[/td][td=bgcolor:#E2EFDA]Olsen,[/td][td=bgcolor:#E2EFDA]Bjern Vito[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jon Rune Hansen[/td][td][/td][td]Jon[/td][td]Rune[/td][td]Hansen[/td][td]Hansen[/td][td]Jon Rune[/td][/tr]
[/table]

 

[tyija1995]

Every name before comma is the last name, rest is first and middlename.

If the name don't have comma, then the last name is lastname.

I have come up with a formula to generate the last name based on the criteria above. It requires the use of 2 UDF as follows:

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

Then try using this formula:

IFERROR(IF(SEARCH(",",B2),EXTRACTELEMENT(B2,1," "),""),EXTRACTELEMENT(B2,COUNTSPACES(B2)+1, " "))

Where B2 contains the cell with the entire name - put this into C2 and it should output the surname.

It will contain a comma if a comma is present in B2, but you can do a find and replace on this at the end after turning it to a value instead of formula.

Now that you have C2 as the surname, do the following in cell D2:

TRIM(REPLACE(B2,SEARCH(C2,B2),LEN(C2),""))

This should output remaining names in to cell D2 which should be all but the surname, then you can re-adjust columns as desired.

 

[Golaidron]

Yeah, but without comma in Lastname column.

 

[sandy666]

this is cosmetic

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1.1[/td][td=bgcolor:#70AD47]Column1.2[/td][td=bgcolor:#70AD47]Column1.3[/td][td=bgcolor:#70AD47]Lastname[/td][td=bgcolor:#70AD47]Names[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mike, John Andrew[/td][td][/td][td=bgcolor:#E2EFDA]Mike,[/td][td=bgcolor:#E2EFDA]John[/td][td=bgcolor:#E2EFDA]Andrew[/td][td=bgcolor:#E2EFDA]Mike[/td][td=bgcolor:#E2EFDA]John Andrew[/td][/tr]

[tr=bgcolor:#FFFFFF][td]John Mike Andrew[/td][td][/td][td]John[/td][td]Mike[/td][td]Andrew[/td][td]Andrew[/td][td]John Mike[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Olsen, Bjern Vito[/td][td][/td][td=bgcolor:#E2EFDA]Olsen,[/td][td=bgcolor:#E2EFDA]Bjern[/td][td=bgcolor:#E2EFDA]Vito[/td][td=bgcolor:#E2EFDA]Olsen[/td][td=bgcolor:#E2EFDA]Bjern Vito[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Jon Rune Hansen[/td][td][/td][td]Jon[/td][td]Rune[/td][td]Hansen[/td][td]Hansen[/td][td]Jon Rune[/td][/tr]
[/table]

now I would like to know : do you have PowerQuery (Get&Transform) ?
and maximum words in one record

 

[Golaidron]

Nope, sorry

 

[sandy666]

Pity, so use any solution from others

have a nice day

btw. PowerQuery add-in for Excel 2010/2013, built-in for Excel 2016 and above (aka Get&Transform)

 

[tyija1995]

If you want to get rid of the commas all in one go I have come up with a formula to do so which is pretty messy!

Remember that EXTRACTELEMENT and COUNTSPACES must be defined from my earlier reply.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Jamesson, Adam Mark Terry[/TD]
[TD]Jameson[/TD]
[TD]Adam Mark Terry[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]Peter Richard Clark[/TD]
[TD]Clark[/TD]
[TD]Peter Richard[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]Thatcher, Liz Amy[/TD]
[TD]Thatcher[/TD]
[TD]Liz Amy[/TD]
[/TR]
</tbody>[/TABLE]

This is the result of the below formula. Which just needs dragging down the C & D columns.

[TABLE="class: grid, width: 1000, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Jameson, Adam Mark Terry[/TD]
[TD]IFERROR(IF(SEARCH(",",IFERROR(IF(SEARCH(",",B2),EXTRACTELEMENT(B2,1," "),""),EXTRACTELEMENT(B2,COUNTSPACES(B2)+1, " "))),LEFT(IFERROR(IF(SEARCH(",",B2),EXTRACTELEMENT(B2,1," "),""),EXTRACTELEMENT(B2,COUNTSPACES(B2)+1, " ")),LEN(IFERROR(IF(SEARCH(",",B2),EXTRACTELEMENT(B2,1," "),""),EXTRACTELEMENT(B2,COUNTSPACES(B2)+1, " ")))-1),IFERROR(IF(SEARCH(",",B2),EXTRACTELEMENT(B2,1," "),""),EXTRACTELEMENT(B2,COUNTSPACES(B2)+1, " "))),IFERROR(IF(SEARCH(",",B2),EXTRACTELEMENT(B2,1," "),""),EXTRACTELEMENT(B2,COUNTSPACES(B2)+1, " ")))[/TD]
[TD]IFERROR(IF(SEARCH(",",B2),MID(TRIM(REPLACE(B2,SEARCH(C2,B2),LEN(C2),"")),3,100),""),TRIM(REPLACE(B2,SEARCH(C2,B2),LEN(C2),"")))[/TD]
[/TR]
</tbody>[/TABLE]

 

[Golaidron]

Thanks for all the help guys and girls.
Will try your last post tyija1995, just need to translate it to norwegian excel

 

[tyija1995]

Hope it works Golaidron.

I have modified the formula so it is a lot shorter now. With full name in Cell B2, try these formulas in cells C2 and D2:

C2:
SUBSTITUTE(IFERROR(IF(SEARCH(",",B2),EXTRACTELEMENT(B2,1," "),""),EXTRACTELEMENT(B2,COUNTSPACES(B2)+1, " ")),",","")

D2:
SUBSTITUTE(TRIM(REPLACE(B2,SEARCH(C2,B2),LEN(C2),"")),", ","")

Substitutes out the comma with nothing - should work the same as the lengthy formula i posted before.

 

[Golaidron]

Hi

It sort of work.

Extractelement and Countspaces are not in the Norwegian Excel, can't find any translation on google either.

Thanks for all you help tyija1995