Split Forename and Surname
The following formulas are useful when you have one cell
containing text which needs to be split up.
One of the most common examples of this is when a person’s
Forename and Surname are entered in full into a cell.
The formula uses various text functions to accomplish the
task.
Each of the techniques uses the space between the names
to identify where to split.
Finding the First Name
Full Name
|
First Name
|
|
Paul Turner
|
Paul
|
=LEFT(C14,FIND(" ",C14,1))
|
Alex McLaren
|
Alex
|
=LEFT(C15,FIND(" ",C15,1))
|
Claire Connelly
|
Claire
|
=LEFT(C16,FIND(" ",C16,1))
|
Finding the Last Name
Full Name
|
Last Name
|
|
Paul Turner
|
Turner
|
=RIGHT(C22,LEN(C22)-FIND(" ",C22))
|
Alex McLaren
|
McLaren
|
=RIGHT(C23,LEN(C23)-FIND(" ",C23))
|
Claire Connelly
|
Connelly
|
=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
|
Billie Claire Darby
|
Darby
|
Paul Timothy Calvin
|
Calvin
|
Darren Michael Smith
|
Smith
|
=RIGHT(C37,LEN(C37)-FIND("#",SUBSTITUTE(C37,"
","#",LEN(C37)-LEN(SUBSTITUTE(C37,"
","")))))
|
|
Finding the Middle name
Full Name
|
Last Name
|
Billie Claire Darby
|
Claire
|
Paul Timothy Calvin
|
Timothy
|
Darren Michael Smith
|
Michael
|
=LEFT(RIGHT(C45,LEN(C45)-FIND("
",C45,1)),FIND(" ",RIGHT(C45,LEN(C45)-FIND("
",C45,1)),1))
|
|
No comments:
Post a Comment