Subscribe Us

Enter your email address:

Loading

Split Forename and Surname with Excel

Labels:


The following formulas are useful when you have one cell containing text that needs to be split up.
One of the most common examples of this is when a persons Forename and Surname are entered in full into a cell.

The formulas use various text functions to accomplish the tasks. Each of the techniques uses the space between the names to identify where to split.

Finding the First Name

Full Name
First Name


Alan Jones
Alan
 =LEFT(C14,FIND(" ",C14,1))
Bob Smith
Bob
 =LEFT(C15,FIND(" ",C15,1))
Carol Williams
Carol
 =LEFT(C16,FIND(" ",C16,1))

Finding the Last Name

Full Name
Last Name

Alan Jones
Jones
 =RIGHT(C22,LEN(C22)-FIND(" ",C22))
Bob Smith
Smith
 =RIGHT(C23,LEN(C23)-FIND(" ",C23))
Carol Williams
Williams
 =RIGHT(C24,LEN(C24)-FIND(" ",C24))

Finding the Last name when a Middle name is present

The formula above cannot handle any more than two names. If there is also a middle name, the last name formula will be incorrect. To solve the problem you have to use a much longer calculation.

Full Name
Last Name
Alan David Jones
Jones
Bob John Smith
Smith
Carol Susan Williams
Williams
=RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37," ","#",LEN(C37)-LEN(SUBSTITUTE(C37," ","")))))

Finding the Middle name


Full Name
Middle Name
Alan David Jones
David
Bob John Smith
John
Carol Susan Williams
Susan
=LEFT(RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)-FIND(" ",C45,1)),1))

'



4 comments:

Post a Comment

Post a Comment