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 using the delimiter or Separator a function. Another method to separate the names would be to use Formula.
Separate name and surname in Excel
The main need to separate the first and last name in Excel is to classify a group of employees or students and to classify them according to their first name or last name. You can do it in two ways:
- Split the full name into first and last name in Excel using the Delimiter or Separator function
- Divide the full name into first, middle, and last name in Excel using a formula
1) Divide the first and last name in Excel using the Separator or Separator function
The easiest way to divide a name into first and last name is using the Delimiter or Separator function. However, for names with first names, the process can be a little complex because the middle and last names can merge into the same column.
The function would call Delimiter or Separator according to the Excel editor that you want to use. Although the procedure would be similar.
Using the delimiter or Separator the function is simple:
Select the list of full names you want to divide into first and last name.
Click on The data tab and select Split the text into columns.
In the drop-down menu that appears on the screen, select the option for the spaces only.
Basically, we separate the words according to the spaces, which is the separating characteristic between the first name and the last name. The problem is that if the names have a middle name, it will appear in the second column and the last name in the third column. This would mean that all surnames will not be in the same column.
2) Separate the full name into first, middle, and last name using the formula
The use of formulas to separate first, middle and last names has the advantage of being able to designate separate columns for each given name, first name and last name.
Suppose we have a column with the list of full names and three columns for lists of first names, first names and last names.
The syntax of the formula for the first name would be:
=LEFT(,SEARCH(" ", ))
The syntax of the formula for the middle name would be:
=MID(,SEARCH(" ", ,1)+1,SEARCH(" ", ,SEARCH(" ", ,1)+1)-SEARCH(" ", ,1))
The syntax of the formula for the surname would be:
=RIGHT(,LEN -SEARCH(" ", ,SEARCH(" ", ,SEARCH(" ", )+1)))
We can use these formulas in the same row cells as the appropriate columns, and then extract the formula with the Fill option.
For example. Suppose we have a list of full names in column A from cell A3 to cell A8.
Column B corresponds to first names, C to first names and D to last names. Since the first full name is in cell A3, the corresponding formulas should be written in cells B3, C3 and D3 as they appear in line 3.
The formula to write in cell B3 will be:
=LEFT(A3,SEARCH(" ",A3))
The formula to write in cell C3 will be:
=MID(A3,SEARCH(" ",A3,1)+1,SEARCH(" ",A3,SEARCH(" ",A3,1)+1)-SEARCH(" ",A3,1))
The formula to write in cell D3 will be:
=RIGHT(A3,LEN(A3)-SEARCH(" ",A3,SEARCH(" ",A3,SEARCH(" ",A3)+1)))
Then, these formulas must be lowered to line 7 after activating the Fill option.
This will help you create the desired sheet by dividing the first name, middle name and last name.