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.

How to Split Names with Comma in Excel: 3 Suitable Ways

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.

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.

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

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

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

Read More: How to Split Names in Excel into Two Columns

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.

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

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

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

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

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

Read More: Excel VBA: Split First Name and Last Name

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.

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

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

This will split middle names in Excel with comma.

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.

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

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

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

Read More: How to Separate First Name Middle Name and Last Name in Excel Using Formula

Download Practice Workbook

Conclusion

To sum up, these are 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.

Related Articles

• How to Split Names into Three Columns in Excel
• Separate First and Last Name with Space Using Excel Formula
• How to Split Names Using Formula in Excel