Excel split cells with full name into 2 columns.

Golaidron

Board Regular
Joined
Jan 23, 2013
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,005
Arr all names separated by a comma or just a space?
 

Golaidron

Board Regular
Joined
Jan 23, 2013
Messages
58
Office Version
  1. 365
Platform
  1. Windows
Some has comma and some has space

3 first examples has comma
rest has space
 
Last edited:

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,757
how do you know what is what in case: Mark Adam John Andrew ?
 

Golaidron

Board Regular
Joined
Jan 23, 2013
Messages
58
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

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

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
6,757
this splitting is completely accidental
because in case post#4 last name is Mark not Andrew

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

Column1Column1.1Column1.2Column1.3Column1.4
Lastname, FirstnameLastnameFirstname
Lastname, Firstname MiddelnameLastnameFirstnameMiddelname
Lastname, Firstname Middelname MiddelnameLastnameFirstnameMiddelnameMiddelname
Firstname LastnameFirstnameLastname
Firstname Middelname LastnameFirstnameMiddelnameLastname
Firstname Middelname Middelname LastnameFirstnameMiddelnameMiddelnameLastname
 
Last edited:

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,005

ADVERTISEMENT

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...

Code:
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

Board Regular
Joined
Jan 23, 2013
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Oct 24, 2015
Messages
6,757
Column1Column1.1Column1.2Column1.3Column1.4CustomMerged
Mikelson, Mark Andrew JohnMikelsonMarkAndrewJohnMikelson Mark Andrew John
Mark Andrew John MikelsonMarkAndrewJohnMikelsonMikelsonMark Andrew John
Mark John Mikelson AndrewMarkJohnMikelsonAndrewMikelsonMark John Andrew

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
 
Last edited:

Golaidron

Board Regular
Joined
Jan 23, 2013
Messages
58
Office Version
  1. 365
Platform
  1. Windows
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.


 

Watch MrExcel Video

Forum statistics

Threads
1,109,049
Messages
5,526,488
Members
409,703
Latest member
nbkqsj7

This Week's Hot Topics

Top