Split First and Last name
This tutorial covers how to split first and last names in excel.
Splitting First and Last names from a combined cell in Excel is very easy. The following formula is 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. The technique uses the space between the names to identify where to split. It is assumed that the name to be split is in Cell B3.
To follow along, download the attached worksheet.
Initial Split First and Last Names Syntax:
Finding the “First Name”
=LEFT(B3,FIND(" ",B3,1))
Finding the “Last Name OR Surname”
=RIGHT(B5,LEN(B5 )-FIND(" ", B5))
Finding the “Last Name OR Surname” when a Middle name is present.
The formula above cannot handle any more than two names. If there is a middle name also, the last name formula will not show the correct results. To solve the problem you have to use a much longer formula.
=RIGHT(B7,LEN(B7)-FIND("#",SUBSTITUTE(B7," ","#",LEN(B7)-LEN(SUBSTITUTE(B7," ","")))))
Finding the “Middle Name”
=LEFT(RIGHT(B10,LEN(B10)-FIND(" ",B10,1)),FIND(" ",RIGHT(B10,LEN(B10)-FIND(" ",B10,1)),1))
