Afia
If you want to split names in Excel, this article is for you. Here, we will demonstrate to you 5 effective methods so that you can do the task effortlessly.
Download Workbook
5 Methods to Split Names in Excel
The following Student List table shows ID No, and First Name, Middle Name, Last Name columns. Here, we want to split the Names using 5 different methods. Here, we used Excel 365. You can use any available Excel version.
Method-1: Using Text to Column Command to Split Names
In this method, we will split names by using the Text to Column command.
➤ First of all, we will select the data from cells C5 to C11, and we will go to the Data tab > select Data Tools > Select Text to Column.
A Convert Text to Column Wizard window will appear.
➤ Then, we select Delimited and click Next.
➤ After that, we will mark Comma and Space, and click Next. Here, our names have commas and space.
➤ Now, we have to click on the upward arrow sign to select the Destination. And select the location where you want to place.
➤ Here, we selected cells from E5 to G11.
➤ After that, click on the blue-colored down marked arrow.
Now, we can see the Destination of the data.
➤ Afterward, click Finish.
A warning window will appear.
➤ Click OK.
Finally, we can see the split names in the First Name, Middle Name, and Last Name columns.
Read More: How to Separate First Middle and Last Name in Excel Using Formula
Method-2: Split Names Using LEFT, RIGHT and MID Functions
Here, we will use the LEFT function to split the first name, the MID function to split the middle name, and the RIGHT function to split the last name from the Student List table. Along with these functions, we’ll use another function called the FIND function (you can use the SEARCH function instead of it).
Split First Name
➤ First of all, we will type the following formula in cell E5.
=LEFT(C5,FIND(",",C5)-1)- FIND(“,”,C5)-1 → returns the position before the first comma (,), as FIND(“,”,C5) provides the position of the first comma. We will get the output 4, this will be the number of characters for the LEFT function.
- LEFT(C5,FIND(“,”,C5)-1) → becomes LEFT(C5,4) and returns that number of characters from the left of the string.
➤ After that press ENTER.
Now, we can see the first name in the First Name column.
➤ We will use the Fill Handle tool to drag down the formula.
Finally, we can see all the first names in the First Name column.
Split Middle Name
➤ First of all, we will type the following formula in cell F5.
=MID(C5, FIND(",",C5) + 1, FIND(",",C5,FIND(",",C5)+1) - FIND(",",C5) - 1)- FIND(“,”,C5) – 1 → returns the position before the first comma(,), as FIND(“,”,C5) provides the position of the first comma. We will get output 4.
- FIND(“,”,C5,FIND(“,”,C5)+1) → returns the position after the 2nd comma(,), we will get the output 13.
- FIND(“,”,C5,FIND(“,”,C5)+1) – FIND(“,”,C5) – 1) → subtracts the number of characters before the first comma(,) from the number of characters after the 2nd comma (,). Here the output is 7, which is the number of characters to be extracted by the MID function.
- FIND(“,”,C5) + 1 → returns the position after the 1st comma (,) as FIND(“,”,C5) provides the position of the first comma, we will get the output 6, which is the start number for the MID function.
- MID(C5, FIND(“,”,C5) + 1, FIND(“,”,C5,FIND(“,”,C5)+1) – FIND(“,”,C5) – 1) → becomes MID(C5,7,6), returns the characters from the middle of a text string.
➤ After that, press ENTER.
We can see the middle name in cell F5.
➤ Now, we will drag down the formula with the Fill Handle tool.
Finally, we can see all the middle names in the Middle Name column.
Split Last Name
➤ First of all, we will type the following formula in cell G5.
=RIGHT(C5,LEN(C5)-(FIND(",",C5,FIND(",",C5)+1)+1))- FIND(“,”,C5) + 1 → returns the position after the first comma(,), here FIND(“,”,C5) specifies the position of the first comma. The output is 4.
- FIND(“,”,C5,FIND(“,”,C5)+1)+1 → returns the position after the 2nd comma(,), as started finding the comma after the position of its first occurence. The output is 14.
- LEN(C5) → the LEN function returns the number of characters in a text string. Here, it returns output 19.
- LEN(C5)-(FIND(“,”,C5,FIND(“,”,C5)+1)+1) → subtracts the number of characters after 2nd comma from total number of characters, and it returns the number of characters for RIGHT function. The output is 5.
- =RIGHT(C5,LEN(C5)-(FIND(“,”,C5,FIND(“,”,C5)+1)+1)) → becomes RIGHT(C5,5), and this returns the characters from the right in a text string.
➤ After that, press ENTER.
Now, we can see the last name in cell G5.
➤ We will drag down the formula with the Fill Handle tool.
Finally, we can see the split names in the First Name, Middle Name, and Last Name columns.
Read More: How to Split Names into Three Columns in Excel (3 Methods)
Method-3: Split Names Using Excel Flash Fill
In this method, we can split names quickly by using the Flash Fill option.
➤ First of all, we will type 2 first names in the First Name column, and press ENTER.
➤ After that, go to the Data tab > select Data Tools > select Flash Fill.
We can see that all the first names automatically fill the column First Name.
➤ For the middle name, in the Middle Name column, we will type 2 middle names, and press ENTER.
➤ After that, go to the Data tab > select Data Tools > select Flash Fill.
Now, we can see all the middle names in the Middle Name column.
➤ Similarly, for the last name, in the Last Name column, we will type 2 last names, and press ENTER.
➤ After that, go to the Data tab > select Data Tools > select Flash Fill.
Now, we can see all the last names in the Last Name column.
We can also see that all the names are split into the First Name, Middle Name, and Last Name columns.
Read More: How to Split Names with Comma in Excel (3 Suitable Ways)
Method-4: Using Table to Split Names
Here, we will insert a table, and after that, we will show you how easily you can split names.
➤ First of all, we will select the entire dataset, we will go to Insert > select Table.
➤ Then, we will make sure that My table has headers is marked and click OK.
Afterward, we can see the creation of a table.
➤ Now, we will type the 1st first name in the column First Name, and press ENTER, After that, we will start to type the 2nd first name and we will notice that all the other first names appear.
➤ At this moment, we will not finish typing the 2nd first name rather we will simply press ENTER.
Finally, we can see all the first names in the First Name column.
Similarly, we will see all the middle names appear in the Middle Name column.
➤ We will press ENTER.
Now, we can see all the middle names in the Middle Name column.
➤ In the same way, we will get the last names in the Last Name column.
Finally, we can see the split first name, middle name, and last name in the columns First Name, Middle Name, and Last Name.
Read More: How to Split Names in Excel into Two Columns (4 Quick Ways)
Method-5: Using Power Query
Power Query is a handy and effective feature to split names in Excel.
➤ First of all, we will select the entire dataset.
➤ After that, we will go to the Data tab > select From Table/Range. This is the way to open Power Query Editor from Excel.
➤ Then, make sure My table has headers is marked, and click OK.
Now, we can see a Table3-Power query window appears. We’ve other tables that’s why Table3, the name can be another for your case.
➤ Now, we will select Split Column > select By Delimiter.
A Split Column by Delimiter window will appear.
➤ We will select Comma in the Select or enter delimiter box.
➤ Click OK.
Now, we can see the split first name, middle name, and last name in three different columns.
➤ After that, we will edit the column name by double-clicking on the column name.
We can see the First Name, Middle Name, and Last Name columns.
Now, we want to load these columns into our workbook.
➤ To do so, we will select the File tab > select Close & Load > select Close & Load To.
An Import Data window will appear.
➤ We will select Existing worksheet, and click on the upward marked arrow. You can select the New worksheet option if you prefer.
➤ Now, we will select from cells D4 to F11, and click on the blue-colored down arrow.
➤ Then, we will click OK.
Finally, we can see the split names in our worksheet.
Conclusion
Here, we tried to show you 5 methods to split names in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, feel free to know us in the comment section.
