Excel Split Name

Last Updated :

Blog Author :

Edited by :

Reviewed by :

Table Of Contents

Split Name in Excel

To split name in Excel (first name, the middle name and the last name), there are two easy ways - the text to column method and the formula method. In the text to column method, a delimiter character needs to be specified. In the formula method, the LEFT, FIND, RIGHT, and LEN functions are used to split names.

Split Name in Excel

How to Separate Names in Excel?

Let us go through the different methods to split names in Excel.

#1 - Text to Column Method

The following data contains the full names of cricket players across different nations. We want to extract the first name and the last name.

The steps of the text to column method are listed as follows:

Select the "full name" column.
In the Data tab, click on the option "text to columns".
The box "convert text to columns wizard" opens.
Select the file type "delimited" and click on "next".
We need to select the type of delimiter that the data contains. In the "full name" column, a "space" separates the first name and the last name. So, we choose "space" as the delimiter option and click "next".
Clicking on "next" will open the box "cconvert text to columns wizard". Here, we choose the cell in which we want to store the first name and the last name.
Click on "finish" and the names appear in two separate columns.

The names in the sixth and the seventh row have been split into three parts-first name, middle name, and last name. The third name is extracted to the extra column.

#2 - Formula Method

The names can also be separated with the help of Excel formulas. For this, we use the LEFT, FIND, RIGHT, and LEN functions.

The steps of the formula method are listed as follows:

Step 1: Open the LEFT function in cell B2. The LEFT function of Excel helps extract characters from the left side of the “full name” column.

Step 2: For the “text” argument of the LEFT function, we choose the cell A2. The “text” is the string from which we want to extract values.

Step 3: We need to specify the number of characters to be extracted from the left side of the selected text. Thus, from the name “Virat Kohli,” we want to extract five characters to obtain the first name.

Step 4: The LEFT formula gives the first name “Virat.”

The last name “Kohli” also has five characters. However, the subsequent names have different characters. Hence, we need to supply the numbers manually to extract text from the left side.

An alternative approach is to apply the FIND function.

Step 5: Open the FIND function to find the first space character in the full name.

Note: The FIND function returns the position of a specified character within the supplied text string.

Step 6: In the first argument of the FIND function, we find the space character by entering a blank between a pair of inverted commas.

Step 7: In the argument “within_text,” we specify the cell reference in which “space” has to be found. So, we select cell A2.

Step 8: In the argument “start_num,” we specify the position of the character from where the search will begin. So, we enter 1 here.

Step 9: The formula returns 6, which is the position of the first space character in cell A2. With the help of the FIND function, we can find the number of characters to be extracted from the left side of the text string.
Step 10: We enter the complete formula “=LEFT(A2,FIND(“ ”,A2,1)-1) to obtain the first names from the “full name” column.

Note: We subtract 1 from the FIND function. Since we do not require the space character in the first name, we reduce the number of characters by one.

Step 11: Open the RIGHT function of Excel to extract the last name from the right side.

Step 12: Enter the formula “=RIGHT(A2,LEN(A2)-FIND(“ ”,A2,1)).” The names appear in two separate columns, as shown in the succeeding image.

Since we do not know the number of characters to be extracted as the last name, we use the FIND and LEN of Excel as supporting functions.

Note 1: The LEN function returns the number of characters in the text string. To extract the last name after space, we ignore the position of the space from the total number of characters.

Note 2: In the case of a middle name, the formula extracts the middle and the last name as the “last name.” It is complicated to extract the middle name using the formula method.

Frequently Asked Questions

#1 - How to extract the first and the last name when the full name contains a comma?

The formula to extract the first name is stated as follows:
"=RIGHT(cell,LEN(cell)-SEARCH(" ",cell))"
The formula to extract the last name is stated as follows:
"=LEFT(cell,SEARCH(" ", cell)-2)"
"Cell" is the cell reference containing the "full name".
Note: The SEARCH function determines the position of the space character. We subtract 2 from the LEFT formula because it accounts for two characters-a comma and space.

#2 - How to split the full name into first, middle, and last name in Excel?

The subsequent formulas are applied when the names are in the formatu2013first name, middle name, and last name.
The formula to extract the first name is stated as follows:
"=LEFT(cell,SEARCH(" ",cell)-1)"
The formula to extract the middle name is stated as follows:
"=MID(cell,SEARCH(" ",cell)+1,SEARCH(" ",cell,SEARCH(" ",cell)+1)-SEARCH(" ",cell)-1)"
The formula to extract the last name is stated as follows:
"=RIGHT(cell,LEN(cell)-SEARCH(" "2,cell,SEARCH(" ",cell,1)+1))"
"Cell" is the cell reference containing the "full name".

#3 - How to separate names in Excel 2013, 2016, and 2019?

The steps to separate names with the help of flash fill are listed as follows:

- Add the "first name" column next to the "full name" column.
- In the first row, enter the name in the "first name" column that you want to extract.
- In the second row, enter the name in the second cell of the "first name" column.
- Excel senses the pattern and populates the first names in all cells automatically.
Press the "Enter" key.

Note: If the flash fill feature is not enabled, click the "flash fill" button in the Data Tools group (in the Data tab).