How to Split Names with Comma in Excel (3 Suitable Ways)

In Excel, you may run into situations where you have to split names inside of cells separated with the comma a lot. In the event, you may need to split them into first names, last names, and in some cases, middle names too. This article will show you the major ways to split names with the comma in Excel.

These methods can also be used to split addresses, phone numbers, etc. separated by commas.


Download Practice Workbook

Download the workbook with the dataset used to describe this article with different methods in different spreadsheets from below. Try downloading and practicing yourself while you go through the tutorials.


3 Easy Ways to Split Names with Comma in Excel

There are three methods I am going to describe here to split names in Excel with a comma. Extracting first name, last name, or middle name has different formulas. I will be going through each in its sub-sections. Go through each section to see how these methods work or select the one you need from the table above.

First, for demonstration, I am using the following dataset.

split names in excel with comma

I am using names containing first name, middle name, and last name. But you can also apply all the methods for just first name and last name separated by a comma.


1. Split Names with Comma Using Text to Columns in Excel

Excel provides a Text to Columns tool to split text values into different column cells separated by delimiters. Likewise, if we use a comma as a delimiter in this tool, we can split names with a comma in Excel. Follow these steps to see how.

Steps:

  • First, select all the cells containing cells separated by a comma. In this example, it is the range of cells B5:B8.

  • Now, in your ribbon, go to the DataΒ tab.
  • Under the Data Tools group, select Text to Columns.

split names in excel with comma

  • As a result, a Convert Text to Column Wizard will pop up. Check Delimited in the first window and click on Next after that.

  • In the second window, check Comma under Delimiters. Then click on Next.

split names in excel with comma

  • In the next window, select the destination where you want to place your separated column. In this case, I have selected cell $C$5.

split names in excel with comma

  • Finally, click on Finish.
  • In case there is an error warning, click on OK.

After that, you will have your first name, middle name, and last name separated.

split names in excel with comma


2. Utilizing Flash Fill to Split Names with Comma

There is a Flash Fill feature from Excel 2013 onwards. To summarize its function, the Flash Fill feature detects the pattern and automatically suggests and fills up the rest of the data. This method can be particularly helpful to split data separated by a delimiter. For the most part, this is the fastest method to split names with a comma in Excel.

Follow these steps for a detailed guide to this feature.

Steps:

  • First, let’s fill out the first names. To do that, select a cell and manually type in the first name of the first entry.

  • Similarly, start typing out the first name for the next entry. The Flash Fill feature will automatically suggest the rest of the first names.

split names in excel with comma

  • Once the names are suggested press Enter on your keyboard. You will have your first names separated.

split names in excel with comma

  • In the same way, fill out the middle name and last name column by repeating the process. You will have your names split.

split names in excel with comma


3. Applying Different Formulas in Excel

You can achieve a similar result to the above two methods by using formulas. Although the result may be the same, you need different approaches in formulas to extract the different portions of the names. For an easier understanding, I have separated the three into their category.


3.1 Split First Name

To split first names we can use a combination of the LEFT and SEARCH functions.

The LEFT function takes a text as a primary argument and the number of characters to be extracted as the optional argument. It returns several characters specified in the argument from the string.

The SEARCH function returns the number of the first position of a specific character. It takes two primary arguments- the characters it should find and the text value where it should find the character. The function can take other another optional argument of where it should start the search.

To know the details of the usage of the formula follow these steps.

Steps:

  • First, select the cell where you want the first name. In this case, it is cell C5.
  • Write down the following formula in the cell.

=LEFT(B5,SEARCH(",",B5)-1)

split names in excel with comma

  • After that, press Enter on your keyboard. You will have your first name separated from the cell.

  • Now, select the cell again and click and drag the Fill Handle Icon to the end of the list to get all the first names from the list.

split names in excel with comma

You will have your first names split with a comma using the formula in Excel.

πŸ” Breakdown of the Formula:

πŸ‘‰ SEARCH(β€œ,”, B5) searches for a comma in cell B5 and returns the first position of a comma in it, which is 5.

πŸ‘‰ SEARCH(β€œ,”, B5)-1 returns the position before the first comma, i.e. the length of the first name which is 4 here.

πŸ‘‰ LEFT(B5, SEARCH(β€œ,”, B5)-1) returns the first four characters from the left of the string which is Alex.


3.2 Split Middle Name

To extract the middle name a combination of the MID and SEARCH functions.

The MID function takes a text, starting position, and several characters as arguments. It returns the characters from the middle of the string.

The SEARCH function returns the number of the first position of a specific character. It takes two arguments- the characters it should find and the text value where it should find the character and an optional argument of where it should start the search.

Steps:

  • First, select the cell where you want to split out the middle name. In this case, it is cell D5.
  • Then, write down the following formula in the cell.

=MID(B5,SEARCH(" ",B5,1)+1,SEARCH(" ",B5,SEARCH(" ",B5,1)+1)-SEARCH(" ",B5,1)-2)

  • After that, press Enter on your keyboard. You will have the middle name extracted from cell B5.

split names in excel with comma

  • Now, select the cell again. Click and drag the Fill Handle Icon to the rest of the column to fill it out with middle names.

split names in excel with comma

This will split middle names in Excel with comma.

πŸ” Breakdown of the Formula:

πŸ‘‰ SEARCH(” β€œ,B5,1) searches for the first space in cell B5 Β and returns 6.

πŸ‘‰ SEARCH(” β€œ,B5,SEARCH(” β€œ,B5,1)+1) returns the second space in the string. It uses the logic of finding space after the first space. The formula returns 16 for cell B5.

πŸ‘‰ SEARCH(” β€œ,B5,SEARCH(” β€œ,B5,1)+1)-SEARCH(” β€œ,B5,1) returns the length between the first space and the second space including the space, which is 10 here.

πŸ‘‰ Finally MID(B5,SEARCH(” β€œ,B5,1)+1,SEARCH(” β€œ,B5,SEARCH(” β€œ,B5,1)+1)-SEARCH(” β€œ,B5,1)-2) returns a total of 8 characters (-2 to reduce the comma and space from the 10 characters)Β  from the value of cell B5 starting from the position 6. In this case, it is Patricia.


3.3 Split Last Name

To split last names from the dataset, we can use a combination of the LEN, RIGHT, and SEARCH functions.

The LEN function takes a text string as an argument and returns the total number of characters in it.

The RIGHT function takes a text and, sometimes, the length to be extracted as arguments. It returns a specific number of characters from the end of the string.

The SEARCH function searches a specific character or a set of characters from a text and returns the position where it first matches. This function generally takes two primary arguments- the characters it should find and the text in which it will search for the characters. It sometimes can take another optional argument of where it will start its search.

Steps:

  • First, select the cell where you want to write the last name. I have selected cell E5 for this.
  • Then write down the following formula.

=RIGHT(B5,LEN(B5)-SEARCH(" ",B5,SEARCH(" ",B5,SEARCH(" ",B5)+1)))

  • After that, press Enter on your keyboard. You will have the last name from cell B5.

split names in excel with comma

  • Select the cell again. Finally, click and drag the Fill Handle Icon to fill out the formula for the rest of the column.

split names in excel with comma

Thus, for the last names, you will have split names in Excel with a comma.

πŸ” Breakdown of the Formula:

πŸ‘‰ LEN(B5) returns the total number of characters in cell B5 and returns 22.

πŸ‘‰ The SEARCH(” β€œ,B5) returns the first position of space which is 6.

πŸ‘‰ SEARCH(” β€œ,B5,SEARCH(” β€œ,B5)+1) returns the position of the second space, which is 16 here.

πŸ‘‰ The nested SEARCH(” β€œ,B5,SEARCH(” β€œ,B5,SEARCH(” β€œ,B5)+1)) indicates the total length from the start to the second space which is still 16.

πŸ‘‰ LEN(B5)-SEARCH(” β€œ,B5,SEARCH(” β€œ,B5,SEARCH(” β€œ,B5)+1)) returns the total number of characters after the second space, which is 6 here. This number of characters will be extracted.

πŸ‘‰ Finally, RIGHT(B5,LEN(B5)-SEARCH(” β€œ,B5,SEARCH(” β€œ,B5,SEARCH(” β€œ,B5)+1))) function takes the text value of cell B5 and returns the number of 6 characters from the end which, in this case, is Morgan.


Conclusion

To sum up, these were the three methods you can use to split names with a comma in Excel. Hope you have found this article informative and helpful. If you have any questions or suggestions, let us know below. For more detailed guides like this, visit Exceldemy.com.

Abrar Niloy

Abrar Niloy

Hi! my name is Abrar-ur-Rahman Niloy. I have completed B.Sc. in Naval Architecture and Marine Engineering. I have found my calling, if you like, in Data Science and Machine Learning and in pursuing so, I have realized the importance of Data Analysis. And Excel is one excel-lent tool do so. I am always trying to learn everyday, and trying to share here what I am learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo