How to Add Comma in Excel Between Names (4 Suitable Ways)

If you are searching for the solution or some special tricks to how to add comma in Excel between names then you have landed in the right place. There are some ways to add comma in Excel between names. This article will show you each and every step with proper illustrations so you can easily apply them for your purpose. Let’s get into the main part of the article.


Download Practice Workbook

You can download the practice workbook from here:


4 Ways to Add Comma in Excel Between Names

In this section, I will show you 4 quick and easy methods to add comma in Excel between names on Windows operating system. You will find detailed explanations of methods and formulas here. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If any methods won’t work in your version then leave us a comment.

How to Add Comma in Excel Between Names


1. Use Find & Replace Feature to Add Comma Between Names

You can use the Find & Replace feature of Excel to find the space between names and replace it with a comma. Follow the steps for this-

πŸ“Œ Steps:

  • First, paste the Names into the Output column then select the cells.
  • Now, go to the Home tab >> Find & Select option >> ReplaceΒ option.

How to Add Comma in Excel Between Names

  • Then a window named β€œFind and Replace” will come.
  • Just enter a space in the β€œFind What” box.
  • Then, enter a comma and a space β€œ, β€œ in the β€œReplace With” box.
  • Finally, press the Replace All

How to Add Comma in Excel Between Names

  • As a result, you will see the names are now separated with commas in between them in the output column.

How to Add Comma in Excel Between Names

Read More: How to Separate Address in Excel with Comma (3 Easy Methods)


2. Use SUBSTITUTE Function to Add Comma Between Names

Alternatively, you can use Excel formulas to add commas between the names. Here, I show how to use the SUBSTITUTE function for this-

πŸ“Œ Steps:

  • First, insert this formula into the cell C5
=SUBSTITUTE(B5," ",", ")

Use SUBSTITUTE Function

Use SUBSTITUTE Function

  • As a result, you will have commas between the names in the output column.

Use SUBSTITUTE Function

Note:

You can use the SUBSTITUTE function only if there is one single space between the names.


3. Add Comma Between Names When There Contains Extra Spaces

If there is more than one space between the names and the number of spaces is not homogenous everywhere in the dataset then you have to use the TRIM Function with the SUBSTITUTE function. in case, you use the SUBSTITUTE function alone here, you will get commas for the substitution of each space as shown in the screenshot.

When There Contains Extra Spaces

If you want to add comma between names when there contain extra spaces then follow the steps below-

πŸ“Œ Steps:

  • Paste this formula into cell C5
=SUBSTITUTE(TRIM(B5)," ",", ")

πŸ”Ž Formula Explanation:

  • TRIM(B5) = Eric Lambert Adelyn Long: The TRIM function removes the extra spaces between names.
  • SUBSTITUTE(TRIM(B5),” β€œ,”, β€œ) = Eric, Lambert, Adelyn, Long: Then the SUBSTITUTE function substitutes the space by a comma between names.

When There Contains Extra Spaces

  • Then, drag the fill handle icon to apply the same formula to the other cells. And you will get the output column with commas between the names.

When There Contains Extra Spaces

Read More: How to Put Comma in Numbers in Excel (7 Easy Ways)


4. Add Comma Between Names One by One

Sometimes, you may need to add commas specifically first 1 or 2 or 3 names. In this case, you have to use the REPLACE function with the FIND function. Follow the steps below for this-

πŸ“Œ Steps:

  • Paste this formula into the cell C5
=REPLACE(B5,FIND(" ",B5),1,",")

πŸ”Ž Formula Explanation:

  • FIND(” β€œ,B5) =5: The FIND function is giving the character serial number containing the first space.
  • REPLACE(B5,5,1,”,”) = Eric,Lambert Adelyn Long: Then, the REPLACE function will replace the 5th character with a comma.

Add Comma Between Names One by One

  • Now, drag the fill handle to the two more cells on the right side.
  • As a result, you will see a comma added each time after the words

Add Comma Between Names One by One

  • Now, select the cells B5:D5 and drag the fill handle icon from the bottom-right corner of cell D5 to the last cell of the dataset.

Add Comma Between Names One by One

  • As a result, a similar formula is applied to all of the cells.

Add Comma Between Names One by One

Read More: How to Insert Comma Between Words in Excel (4 Simple Methods)


Conclusion

In this article, you have found how to add comma in Excel between names. In addition, you can add comma if there are extra spaces between the names and also when you have to comma on by one after names. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo