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?

Column1		Column1.1	Column1.2	Column1.3	Lastname	Names
Mike, John Andrew		Mike,	John	Andrew	Mike,	John Andrew
John Mike Andrew		John	Mike	Andrew	Andrew	John Mike
Olsen, Bjern Vito		Olsen,	Bjern	Vito	Olsen,	Bjern Vito
Jon Rune Hansen		Jon	Rune	Hansen	Hansen	Jon Rune

 

[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

Column1		Column1.1	Column1.2	Column1.3	Lastname	Names
Mike, John Andrew		Mike,	John	Andrew	Mike	John Andrew
John Mike Andrew		John	Mike	Andrew	Andrew	John Mike
Olsen, Bjern Vito		Olsen,	Bjern	Vito	Olsen	Bjern Vito
Jon Rune Hansen		Jon	Rune	Hansen	Hansen	Jon Rune

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.

	A	B	C	D
2		Jamesson, Adam Mark Terry	Jameson	Adam Mark Terry
3		Peter Richard Clark	Clark	Peter Richard
4		Thatcher, Liz Amy	Thatcher	Liz Amy

<tbody>
</tbody>

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

	A	B	C	D
2		Jameson, Adam Mark Terry	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, " ")))	IFERROR(IF(SEARCH(",",B2),MID(TRIM(REPLACE(B2,SEARCH(C2,B2),LEN(C2),"")),3,100),""),TRIM(REPLACE(B2,SEARCH(C2,B2),LEN(C2),"")))

<tbody>
</tbody>

 

[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