Let us suppose a situation in which you have a list of full names of people. You intend to split these names into three parts – first name, middle name, and last name. This can be done in Microsoft Excel by using the Delimiter or Separator function. Another method of splitting the names would be using Formula.
Separate First and Last name in Excel
The primary need to separate the First and Last names in Excel is to classify a group of employees or students and categorize them according to their first or last names. You can do this in two ways:
- Split full name to first and last name in Excel using the Delimiter or Separator function
- Split full name to first, middle, and last name in Excel using formula
1] Split first and last name in Excel using Delimiter or Separator function
The easiest method to split a name into first name and last name is using the Delimiter or Separator function. However, for names with middle names, the process could be a little complex since the middle and last names might merge in the same column.
The function would be called Delimiter or Separator depending on which Excel editor you intend to use. However, the procedure would be similar.
Using the Delimiter or Separator function is simple:
Select the list of full names which you intend to split into the first name and last name.
Click on the Data tab and select Split text to columns.
From the drop-down menu that appears on the screen, select the option for spaces only.
Basically, we are separating the words based on spaces, which is the separating feature between the first and last names. The problem is that if the names have a middle name, it will show in the second column and the last name in the third column. It would mean that all last names won’t be in the same column.
2] Separate full name to first, middle, and last name using Formula
The benefit of using formulae for splitting first name, middle name, and last name is that you can designate separate columns for each of the first, middle, and last names.
Let us suppose we have one column with the list of full names and three columns for the lists of first, middle, and last names.
The syntax for the formula for the first name would be:
=LEFT(<first cell with full name>,SEARCH(" ",<first cell with full name>))
The syntax for the formula for middle name would be:
=MID(<first cell with full name>,SEARCH(" ",<first cell with full name>,1)+1,SEARCH(" ",<first cell with full name>,SEARCH(" ",<first cell with full name>,1)+1)-SEARCH(" ",<first cell with full name>,1))
The syntax for the formula for the last name would be:
=RIGHT(<first cell with full name>,LEN<first cell with full name>-SEARCH(" ",<first cell with full name>,SEARCH(" ",<first cell with full name>,SEARCH(" ",<first cell with full name>)+1)))
We can use these formulae in the same row cells of the appropriate columns and then pull the formula down using the Fill option.
Eg. Let us say we have a list of full names in column A from cell A3 to cell A8.
Column B is for first names, C for middle names, and D for last names. Since the first full-name is in cell A3, we would have to write the corresponding formulae in cells B3, C3, and D3 since they are in row 3.
The formula to be written in cell B3 will be:
=LEFT(A3,SEARCH(" ",A3))
The formula to be written in cell C3 will be:
=MID(A3,SEARCH(" ",A3,1)+1,SEARCH(" ",A3,SEARCH(" ",A3,1)+1)-SEARCH(" ",A3,1))
The formula to be written in cell D3 will be:
=RIGHT(A3,LEN(A3)-SEARCH(" ",A3,SEARCH(" ",A3,SEARCH(" ",A3)+1)))
Then, these formulae would have to be pulled down till row 7 after activating the Fill option.
This will help you in creating the desired sheet by splitting the first, middle, and last names.
Read next: Excel opens in a tiny window.
