How to Add Comma Between Names in Excel

Adding a comma between names in Excel can be laborious and time-consuming if done manually, especially if you have a lot of data.

Doing it manually also increases the chances of error.

Thankfully, there are various ways to add commas between names in Excel.

In this tutorial, we are going to show you seven methods to add commas between names. These methods are guaranteed to save you time, as well as speed up your workflow.

Method 1: Use the TEXTJOIN Function to Add a Comma Between Names

Using the TEXTJOIN Function, in my opinion, is the easiest and quickest method to add a comma between names.

Whenever you need to combine multiple text strings separated by a delimiter, then you can use the new TEXTJOIN Function.

If you have Office 2019 or Microsoft 365, then you should have access to the newer TEXT functions, including the TEXTJOIN Function.

Below, we have a list of names in range B3:B7. We would like to have all the names returned in cell D7, with a comma and a space separating each of them.

Below is the formula that will combine these names and add a comma in between (we have entered the formula in cell D7):

=TEXTJOIN(", ",TRUE,B3:B7)

Let’s take a closer look at the syntax of the TEXTJOIN Function:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

• delimiter refers to a character, such as a space or a comma, which will be placed in between the text strings. The delimiter has to be enclosed by double quotation marks. This is a required value. In our example, the delimiter combination is a comma and a space.

So we are telling the TEXTJOIN Function that we want each name to be separated by a comma and a space.

• ignore_empty tells Excel what to do in the case of empty cells. If the value is set to TRUE, then Excel ignores the empty cells. This is a required value.

• text1 can be a text string or an array of strings such as a range. This is a required value. In our example, we have input the range B3:B7. This range contains all the names that we’d like to be returned, separated by our delimiter combination, in cell D7.

• text2 refers to additional text strings that we would like to be joined. This is an optional value.

So, in summary, our function takes the names in the range B3:B7 and concatenates them.

While ensuring that each name is separated by a comma and a space and returns this combined text string in cell D7.

Also read: How to Combine Two Columns in Excel (with Space/Comma)

Method 2: Use the SUBSTITUTE Function to Add a Comma Between Names

If you have an older version of Excel, then you can use the SUBSTITUTE Function to add a comma between names.

The SUBSTITUTE Function is used to replace certain specified text within a text string with new user-specified text.

Below, we have a list of participant names for each day of the week, currently separated by a space, in column C.

We would ultimately like to have a comma space combination instead of a space between these names.

Here are the steps we can use to add a comma in between the names:

1. Apply the formula below in cell E3.

=SUBSTITUTE(C3," ",", ")

2. Drag the formula down the column using the fill handle.

3. Select range E3:E7.

4. Press CTRL-C on the keyboard to copy and then select cell C3.

5. Go to the Home Tab, and in the Clipboard Group, click on the Paste drop-down arrow.

6. Select the Paste Values option.

7. We now want to get rid of the values we do not need. So select range E3:E7 and press delete on the keyboard. You should now see the following.

The syntax of the SUBSTITUTE Function is SUBSTITUTE(text, old_text, new_text, [instance_num]) where:

• text – refers to the entire text or string which the function has to evaluate. This is a required value. In our example, our text is the value in C3.

• old_text – refers to the specific portion or segment of text which you’d like to be substituted. This is a required value. For our example, the old_text is the space. This space occurs between each of the names.

• new_text – refers to the new text that you would like to substitute the old text with. This is a required value. In our example, this is a comma space combination. Since we would like a comma and a space between each of the names.

• instance_num – refers to the occurrences of the old_text which should be replaced. If you specify this value, then only that specific instance will be replaced. If you don’t, then all the instances of the old_text will be substituted.

Since we didn’t specify an instance_num, all the spaces will be replaced with the comma space combination.

Also read: How to Remove Dashes (-) in Excel?

Method 3: Use the Ampersand Operator to Add a Comma Between Names Manually

You can use the Ampersand operator to combine multiple names with a comma separating each of the names.

While this method is not as fast as using the TEXTJOIN Function, you can use it if you have an older version of Excel and you don’t have many names.

Below, we have three names in row 1. We would like to have all the names returned in cell D1, with a comma and a space between them, by using the Ampersand operator.

Here are the steps we can use to add a comma in between the names:

1. Apply the formula below in cell D1.

=A1&", "&B1&", "&C1

The Ampersand operator can be used to combine multiple text strings into one text string.

In our example above, we use the Ampersand operators in a single formula.

We input the cell references we’d wanted to join and then used the Ampersand operators to concatenate each of the text strings, separated by a comma space combination.

Also read: Switch First and Last Name with Comma in Excel (Reverse Names)

Method 4: Use the Find and Replace Feature to Add a Comma Between Names

The Find and Replace Feature is used for advanced searches and replacement mostly.

The advantage of using the Find and Replace Feature is that it gives you more control over the Replace process.

The Find and Replace Feature can be used to locate and replace the spaces between names with a comma.

Below, we have a list of full names in column B, currently separated by spaces. We would like to add a comma between the first names, middle names, and last names.

Here are the steps we can use to add a comma in between the names:

1. Select range B2:B6.

2. Go to the Home Tab, and in the Editing Group, choose the Find & Select option.

3. Select Replace…

4. You should see the Find and Replace Dialog Box.

5. In the Find what: text box, enter a Space. In the Replace with: text box, enter a comma and a space. Then press the Replace All Button.

6. Click Close. You should see the following.

Also read: How to Remove Commas in Excel (from Numbers or Text String)

Method 5: Use the Flash Fill Feature to Add a Comma Between Names

The Flash Fill feature in Excel can be used to emulate detected patterns. We can use Flash Fill to add a comma between names quickly.

Below, we have a list of names in column B. We would like these names to be separated by a comma space combination in column C.

Here are the steps we can use:

1. In cell C1, enter each name in cell B1, separated by a comma and a space: Susan, Erica, James, John.

2. With cell C1 selected, press CTRL + E on your keyboard to apply Flash Fill. You should see the following.

Also read: Merge First and Last Name in Excel

Method 6: Use Power Query to Add a Comma Between Names

Depending on how your data is set up, you can use Power Query to merge columns containing names and insert a comma between each name.

Below, we have an Excel Table containing a first name column, a middle name column, and a last name column.

We want to have one column which contains the first, middle, and last names with a comma between them.

1. Since our data is already in an Excel Table, we simply have to go to the Data Tab and, in the Get & Transform Data Group, select From Table/Range.

Note: It is recommended that you use Power Query with data that is in an Excel Table. If you have data in regular Excel range, it would be a good idea to first convert it into an Excel Table.

2. The Power Query Editor should open.

3. While holding the CTRL key, click on the Middle Name Header and the Last Name Header, so that all three columns are selected.

4. Go to the Transform Tab, and in the Text Column group, click on the Merge Columns option.

5. You should see the Merge Columns Dialog Box.

6. Select –Custom– as the Separator.

7. Enter a comma followed by a space in the textbox.

8. Instead of Merged, enter the text Full Names as the New column name (you can use whatever name you like).

9. Click Ok.
10. You should see that the three columns are now combined in one single column, and there is a comma space separator between the first name, middle name, and last name.

11. Now click on the File Tab, and select Close & Load.

12. You should see the following.

Also read: Apply Comma Style Number Format in Excel

Method 7: Use VBA to Add a Comma Between Names

We can use the VBA Replace Function to assist us with placing a comma between names.

The VBA Replace Function is a TEXT Function that is built-in to VBA. It replaces a specified substring within a text string, with a newer substring.

Below, we have a list of names in column A. We would like to have the names returned in column B, with a comma and a space separating them.

Here are the steps we can use:

1. Select the cells that have the names where you want to add a comma
2. Go to the Developer Tab. In the Code group, click on the Visual Basic option.

3. Once you are in the Visual Basic Editor, go to the Insert Menu and choose the Module item.

4. Type the following code as the sub-procedure.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Sub Add_Comma_Between_Names()

' Loop through each cell in the selected range
    For Each Cell In Selection

        ' Replace spaces in the current cell's value with comma-space (", "), and write the result to the cell in the next column (Offset by 1 column)
        Cell.Offset(0, 1).Value = Replace(Cell, " ", ", ")

    ' Move to the next cell in the loop
    Next Cell

' End the Subroutine
End Sub

4. Press F5 on your keyboard while your cursor is in the sub-procedure to run the code. Press Alt-F11 on the keyboard to return to the worksheet, and you will see the following.

We start our macro by selecting the cells that contain the names.

We then open a For Each Loop structure since we want to loop through all the selected cells. Our offset function is used to set the values in column B.

The VBA Replace function is used to locate the spaces in the adjacent cell in column A, and replace them with the comma space combination in column B.

Finally, we close the Loop structure with the Next keyword.

In this tutorial, we have comprehensively covered ways to add commas between names.

I hope that you have found the techniques that I have illustrated to be helpful.

Other Excel articles you may also like:

• How to Generate Random Names in Excel
• Extract Last Name in Excel
• Separate Comma Separated Values in Excel
• How to Add Text to the Beginning or End of all Cells in Excel
• Remove Middle Name from Full Name in Excel