Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Split Names in Excel (5 Effective Methods)

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.

How to Split Names in Excel


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.

How to Split Names in Excel

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).

How to Split Names in Excel

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.

How to Split Names in Excel

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.

How to Split Names in Excel

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.

How to Split Names in Excel

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.

How to Split Names in Excel

➤ 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.

How to Split Names in Excel

➤ 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.

How to Split Names in Excel

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.

How to Split Names in Excel

➤ 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.

How to Split Names in Excel

Similarly, we will see all the middle names appear in the Middle Name column.

➤ We will press ENTER.

How to Split Names in Excel

Now, we can see all the middle names in the Middle Name column.

How to Split Names in Excel

➤ 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.

How to Split Names in Excel

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.

How to Split Names in 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.

How to Split Names in Excel

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.

How to Split Names in Excel


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.


Related Articles

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo