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 also 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.
- Select the FULL NAME data first.
- Now go to the DATA tab and click on the “Text to Column” 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 first name and last name. In our data “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 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 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 extracted from the left side.
- Now we need to extract the last name which is from the RIGHT side, so open RIGHT function.
- 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 function 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
This has been a guide to Excel Split Name. Here we learn how to separate names in excel using two methods 1) text to column method and 2) formula method with detail examples and downloadable excel templates. You may learn more about excel from the following articles –
- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion
