Nazmul Hossain Shovon
In this tutorial, I will show you 4 simple methods to insert a comma between words in excel. When you have small datasets, inserting commas inside them manually may be a good decision. But if the dataset is very large, then you will need some quick methods to get the job done in a short time. We will see these methods in this tutorial.
Download Practice Workbook
You can download the practice workbook from here.
4 Simple Methods to Insert Comma Between Words in Excel
1. Using Find and Replace Feature
The Find and Replace feature in excel allows you to work with large sets of data with just a few clicks. For this method, we have a dataset of a few categories of names each separated by a space. We will see how we can insert a comma between the words.
Steps:
- First, select the cells from B5 to B8 and press Ctrl+H.
- Now, the Find and Replace window will open.
- Here, click on the Find what field and press the Space key once.
- In addition, in the Replace with field, type one comma and press the Space key.
- Then, click on Replace All.
- Immediately, you will see a message box telling you that the replacements are done.
- So, simply click on OK and Close the Find and Replace window.
- Finally, you will see that Excel has added a comma between the two words for all the cells.
Read More: How to Separate Address in Excel with Comma (3 Easy Methods)
2. Applying SUBSTITUTE Function
If we do not want to change the original data, we can use the SUBSTITUTE function in excel. This will allow us to insert the result in a different cell with a comma between words. Also, this function replaces some parts of a text with a new string.
Steps:
- To begin with, double-click on cell C5 and type in the following formula:
=SUBSTITUTE(TRIM(B5)," ",", ")
- Then, press Enter.
- As a result, Excel will insert a comma between the two words.
- Next, take the mouse pointer to the lower right corner of cell C5 and drag the Fill Handle down.
- This will apply the SUBSTITUTE function to all the cells below and insert a comma in them.
3. Insert Comma Manually Using Ampersand
In the dataset for this method, we have the names of people in column B and their locations in column C. We will join the words in column D and insert a comma between them using the ampersand operator in excel.
Steps:
- First of all, double-click on cell D5 and insert the following formula:
=B5&","&C5
- Next, to apply the formula, press Enter.
- Consequently, excel will add a comma between the two words of cells B5 and C5.
- Finally, copy the formula to the cells below using the Fill Handle.
4. Utilizing TEXTJOIN Function
In this method, we have merged cells C5 and C6, C7 and C8, and C9 and C10. Then, we will use the TEXTJOIN function to insert the words with a comma between them and place them in the merged cells. Let us see how we can do this.
Steps:
- In the beginning, double-click on cell C5 and enter the following formula:
=TEXTJOIN(",",TRUE,B5:B6)
- Now, press Enter and this will insert a comma between the words of cells B5 and B6.
- Finally, copy this formula to the cells below using the Fill Handle.
Insert Comma Between Words in Excel to Create a Running List
A running list is one that keeps adding data as the number of rows increases. We can easily create a running list of words in excel inserting comma between them. Let us see the steps below.
Steps:
- Firstly, copy the value of cell B5 to cell C5.
- Then, double-click on cell C6 and insert the following formula:
=C5&”,”&B6
- Now, press Enter and excel will insert a comma between the two words of cells C5 and B6.
- Finally, copy the above formula to the rest of the cells below, and this will insert a comma between each word.
Read More: How to Insert Comma in Excel for Multiple Rows (3 Suitable Ways)
Conclusion
I hope that you were able to understand the methods I showed in this tutorial on how to insert comma between words in excel. There may arise many situations where you may need to present your data separated by commas. These methods will help you to quickly achieve that. Also, download the workbook and practice with the datasets. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
