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

Method 1 – Split Names with Comma Using Text to Columns in Excel

Steps:

Select all the cells containing cells separated by a comma. In this example, the range of cells is B5:B8.

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

A Convert Text to Column Wizard will pop up. Check Delimited in the first window and click Next.

Check Comma under Delimiters. Click on Next.

Select the destination where you want to place your separated column. We selected cell $C$5.

Click Finish.
There is an error warning; click OK.

You will have your first name, middle name, and last name separated.

Method 2 – Utilizing Flash Fill to Split Names with Comma

Steps:

Fill out the first names. Select a cell and manually type in the first name of the first entry.

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.

Fill out the middle name and last name column by repeating the process. You will have your names split.

Method 3 – Applying Different Formulas in Excel

3.1 Split First Name

Steps:

Select the cell where you want the first name. It is cell C5.
Write down the following formula in the cell.

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

Press Enter on your keyboard. You will have your first name separated from the cell.

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.

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

Steps:

Select the cell where you want to split out the middle name. It is cell D5.
Write down the following formula in the cell.

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

Press Enter on your keyboard. You will have the middle name extracted from cell B5.

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.

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. It is Patricia.

3.3 Split Last Name

Steps:

Select the cell where you want to write the last name. We selected cell E5 for this.
Write down the following formula.

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

Press Enter on your keyboard. You will have the last name from cell B5.

Click and drag the Fill Handle Icon to fill out the formula for the rest of the column.

You will have split names in Excel with a comma for the last names.

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, is Morgan.

Download Practice Workbook

Split Names with Comma.xlsx