Osman Goni Ridwan
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.
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.
- 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
- As a result, you will see the names are now separated with commas in between them in the output column.
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," ",", ")
- Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column or use Excel keyboard shortcuts Ctrl+C and Ctrl+V to copy and paste.
- As a result, you will have commas between the names in the output column.
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.
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.
- 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.
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.
- 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
- 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.
- As a result, a similar formula is applied to all of the cells.
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.
