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.

 

[gallen]

Arr all names separated by a comma or just a space?

 

[Golaidron]

Some has comma and some has space

3 first examples has comma
rest has space

 

[sandy666]

how do you know what is what in case: Mark Adam John Andrew ?

 

[Golaidron]

Column B should contain Mark Adam John
Column C should contain Andrew

In our list that's 99,9% accurate.

 

[sandy666]

this splitting is completely accidental
because in case post#4 last name is Mark not Andrew

any pattern for last name? eg. Mikelson => son

[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]Column1.4[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Lastname, Firstname[/td][td][/td][td=bgcolor:#E2EFDA]Lastname[/td][td=bgcolor:#E2EFDA]Firstname[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Lastname, Firstname Middelname[/td][td][/td][td]Lastname[/td][td]Firstname[/td][td]Middelname[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Lastname, Firstname Middelname Middelname[/td][td][/td][td=bgcolor:#E2EFDA]Lastname[/td][td=bgcolor:#E2EFDA]Firstname[/td][td=bgcolor:#E2EFDA]Middelname[/td][td=bgcolor:#E2EFDA]Middelname[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Firstname Lastname[/td][td][/td][td=bgcolor:#E2EFDA]Firstname[/td][td=bgcolor:#E2EFDA]Lastname[/td][td=bgcolor:#E2EFDA][/td][td=bgcolor:#E2EFDA][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Firstname Middelname Lastname[/td][td][/td][td]Firstname[/td][td]Middelname[/td][td]Lastname[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Firstname Middelname Middelname Lastname[/td][td][/td][td=bgcolor:#E2EFDA]Firstname[/td][td=bgcolor:#E2EFDA]Middelname[/td][td=bgcolor:#E2EFDA]Middelname[/td][td=bgcolor:#E2EFDA]Lastname[/td][/tr]
[/table]

 

[gallen]

This code will split out the names on sheet1 and place them on sheet 2.

The confusion about how to determine which goes in which which column is to be sorted...

Sub SortNames()
    'Assumes code is in same sheet as list of names
    Dim c As Range
    Dim x As Integer, iCol As Integer, lrow As Long
    Dim s As String
    Dim SplitNames() As String
    Dim rNames As Range 'range of names to be separated
    
    Set rNames = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
    
    
    lrow = 2 'first row to put results on sheet 2


    
    For Each c In rNames
        iCol = 1 'reset to first column
        SplitNames() = Split(c, " ")
        For x = 0 To UBound(SplitNames)
            s = SplitNames(x)
            s = Replace(s, ",", "") 'remove any commas
            s = Trim(s) 'remove any leading/trailing spaces
            Sheet2.Cells(lrow, iCol) = s
            iCol = iCol + 1
        Next x
        lrow = lrow + 1
    Next c
    
End Sub

 

[Golaidron]

No pattern at all.

Today i use this formulas:

Example name:
Mikelson, Mark Andrew John
For last name:
=left(A3,(Find(",",A3,1)-1))

Result = Mikelson

For first and middle
=MID(A3,find(",",A3)+2,256)

Result = Mark Andrew John

Mark Andrew John Mikelson
For lastname
=iferror(iferror(right(A9,len(A9)-Search(" ",A9,Search(" ",A9,Search(" ",A9,1)+1)+1)),if(iserror(find(" ",A9,1+find(" ",A9))),mid(A9,find(" ",A9)+1,len(A9)),mid(A9,1+find(" ",A9,1+find(" ",A9)),len(A9)))),if(iserror(find(" ",A9,1+find(" ",A9))),mid(A9,find(" ",A9)+1,len(A9)),mid(A9,1+find(" ",A9,1+find(" ",A9)),len(A9))))

Result = Mikelson

For firstname and middlename
=left(A9,LEN(A9)-(LEN(C9)+1))

Result = Mark Andrew John

These forumlas is also working if i remove 1 or 2 of the middle names.

Just want 1 formulas that covers first + middlename and one forumula that covers lastname.

 

[sandy666]

[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]Column1.4[/td][td=bgcolor:#70AD47]Custom[/td][td=bgcolor:#70AD47]Merged[/td][/tr]

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

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

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Mark John Mikelson Andrew[/td][td][/td][td=bgcolor:#E2EFDA]Mark[/td][td=bgcolor:#E2EFDA]John[/td][td=bgcolor:#E2EFDA]Mikelson[/td][td=bgcolor:#E2EFDA]Andrew[/td][td=bgcolor:#E2EFDA]Mikelson[/td][td=bgcolor:#E2EFDA]Mark John Andrew[/td][/tr]
[/table]

but you still don't know middle name and first name
for last name pattern is son

but again example from post#4: you don't know first name, middle name, middle name and last name

 

[Golaidron]

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.