Excel Split Name

Split Name in Excel

It is quite common that we have full name values in cells but often times we need to split those into First Name, Last Name, and Middle Name. When the data is in excel, we can split names into different columns by using different ways. We have a variety of ways; some of them are quite complicated. In this article, we will show you how to split names in excel.

How to Separate Names in Excel?

We have different methods to split names in excel; we will see each method now in detail.

#1 – Text to Column Method

We have below the full name list in excel.

In the above data, we have FULL NAME of cricket players across different nations. We need to extract First Name & Last Name.

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

• Select the FULL NAME data first.

• Now go to the DATA tab and click on the “Text to Column in Excel” option.

• This will open up “Text to Column Wizard.”

• Make sure “Delimited” is selected.

Click on “Next” to go to the next step.

• In the next step, we need to select the “Delimiter” type, i.e., in the FULL NAME, what is the delimiter character which is separating the first name and last name. In our data, the “space” character is separating names, so choose “Space” as the delimiter option.

Click on Next, and it will go to Step 3.

• In the next step, choose the cell where we need to store our First Name and Last Name.

• Now click on “Finish,” and we will have names in separate columns.

Look in the case of row number 6 & 7, we have three names as “First Name, Last Name, and Middle Name,” so the third name is extracted to the extra column.

#2 – Formula Method

We can also separate names in excel based on formulas as well. We will use LEFT, RIGHT, LEN, and FIND methods.

• Using the LEFT function in excel, we can extract characters from the left side of the FULL NAME. First, open the LEFT function for the B2 cells.

• Text is nothing but from which text we need to extract values, so choose A2 cell.

• Next, we need to mention how many characters we need to extract from the left side of the selected text. So in the name “Virat Kohli,” we need to extract 5 characters to extract the first name.

• So this will give the first name as “Virat.”

For the next name also we have only 5 characters, but for the next names, we have different characters, so this is where supplying numbers manually to extract from the left side. So we need to use the “Find” function to find the first space character in the name.

• Open function to understand how it works.

• Find Text is the first argument, so we need to find space characters and enter the same.

• In which cell we need to find the space character is Within Text, so choose A2 cell.

• The last argument is Start Num, so the first space character we need to find, so enter 1.

• So in the A2 cell first space character position is 6, so using this, we can find how many characters need to be extracted from the left side.

• Now we need to extract the last name, which is from the RIGHT side, so open the RIGHT function in excel.

• For the RIGHT function to we don’t know how many characters are to be extracted as the last name, so for this, also we need to use FIND & LEN in excel as supporting functions.

This time we have used LEN because the LEN function returns how many characters are there in the entire text, and FIND will find the space character, so from the overall number of characters, we need to ignore space positon and after space, we need to extract the last name.

Things to Remember

• Middle Name extraction is complicated using formula.
• FIND will find the supplied character position in the supplied text.
• LEN will return the number of characters in the supplied text value.

Recommended Articles