How to Add Comma in Excel to Concatenate Cells (5 Easy Ways)

Sometimes, we have to represent multiple data in a single concatenated cell in Microsoft Excel. This is mainly used in Schools, Shops, Business Companies, Government Institutions, etc. In order to do so, we need to insert commas between the data in that concatenated cell. Are you having trouble adding commas in Excel concatenate? This tutorial will help you to learn how to add comma in Excel to concatenate cells in 5 easy ways. Let’s get started!


Download Practice Workbook

You can download the Excel workbook from here.


5 Easy Ways to Add Comma in Excel to Concatenate Cells

We can add comma in Excel concatenate in 5 easy ways. The first method is by using the CONCAT function, the second method is by applying the TEXTJOIN function, the third method includes the use of Concatenate method, the fourth one is by combining the CONCAT and the TRANSPOSE function and the fifth one is by using VBA code. Now we will learn all of these 5 methods to add comma in Excel concatenate.


1. Use of CONCAT Function

In order to add comma in Excel to concatenated cells using the CONCAT function, we have taken a dataset like the following figure where we have Student ID numbers of 6 students and their Numbers.

Suitable Ways to Add Comma in Excel to Concatenate Cells

Now we will add comma to concatenated cells using the CONCAT function. In order to do so, follow the steps below.

Steps:

  • First, select cell D5 and type the following formula:
=CONCAT(B5,",",C5)
  • Now, press Enter.
  • As a result, you will see an output like the image below where the first student’s Student ID and his Number will be added in a cell under Concatenated Cell column.

Suitable Ways to Add Comma in Excel to Concatenate Cells

  • Second, select cell D5 and drag the Fill Handle to the entire column Concatenated Cell.
  • Finally, you will see an output like the one below where all Student IDs and their Numbers will be added in the concatenated cells.

Read More: How to Use Comma in Excel Formula (2 Suitable Ways)


2. Applying TEXTJOIN Function

We can also add comma in Excel to concatenate cells applying TEXTJOIN Function. To do so, we have taken the previous dataset like the following figure where we have Student ID numbers of 6 students and their Numbers.

Suitable Ways to Add Comma in Excel to Concatenate Cells

Now we will add commas to concatenated cells using the TEXTJOIN function. In order to do so, follow the steps below.

Steps:

  • To begin with, select cell D5 and type the following formula:
=TEXTJOIN(",",TRUE,B5:C5)
  • Now, press Enter.
  • As a result, you will see an output like the image below where first student’s Student ID and his Number will be added in a cell under Concatenated Cell column.

Suitable Ways to Add Comma in Excel to Concatenate Cells

  • Second, select cell D5 and drag the Fill Handle to the entire column Concatenated Cell.
  • Finally, you will see an output like the one below where it will add all Student IDs and their Numbers in the concatenated cells.

Read More: How to Put Comma After 2 Digits in Excel (9 Quick Methods)


3. Using Ampersand Operator

If we want to add comma in Excel to concatenated cells using the Ampersand Operator, we have to take the same dataset of Student ID numbers of 6 students and their Numbers like the following figure.

Suitable Ways to Add Comma in Excel to Concatenate Cells

Now we will add commas to concatenated cells using the Concatenate method. In order to do so, follow the steps below.

Steps:

  • First, select cell D5 and type the following formula:
=B5&","&C5
  • Now, press Enter.
  • As a result, you will see an output like the image below where first student’s Student ID and his Number will be added in a cell under Concatenated Cell column.

Suitable Ways to Add Comma in Excel to Concatenate Cells

  • Second, select cell D5 and drag the Fill Handle to the entire column Concatenated Cell.
  • Finally, you will see an output like the one below where all Student IDs and their Numbers will be in the concatenated cells.

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


4. Combine CONCAT and TRANSPOSE Functions

Now we will see how to combine CONCAT and TRANSPOSE functions to add comma in Excel to concatenate cells. For this example, we will need a dataset like below where the Name, E-mail, City, and Country of a person. We will use CONCAT and TRANSPOSE functions to combine these data in a single concatenated cell and put comma between these data in a merged cell from (B10:C10).

Suitable Ways to Add Comma in Excel to Concatenate Cells

In order to do so, follow the steps below:

Steps:

  • First, select the merged cell B10:C10.
  • Second, type the following formula:
=CONCAT(TRANSPOSE(C4:C7)&",")
  • Then, press Enter.
  • Finally, you will see an output like the image below where commas will be added between all the data from C4 to C7 in the Concatenated cell.

Suitable Ways to Add Comma in Excel to Concatenate Cells

Read More: How to Add a Comma Between City and State in Excel (6 Ways)


5. Inserting VBA Code

We can add comma in Excel to concatenate cells using VBA Code. Actually, Excel needs an user-defined function to do the job. First, we have to create a user-defined function named CONCATENATEMANY using VBA Code. Then we will be able to use the CONCATENATEMANY function to add comma in a merged cell (B8:E8) from a dataset of the Name, E-mail, City, and Country of a person.

Suitable Ways to Add Comma in Excel to Concatenate Cells

In order to add comma in an Excel Concatenated cell between the given data of the person, follow the steps below.

Steps:

  • First, open a blank workbook in Microsoft Excel.
  • Then, go to the Developer tab and click on Visual Basic to open the Microsoft Visual Basic editor.

Suitable Ways to Add Comma in Excel to Concatenate Cells

  • Now, click on the Insert tab and click Module as shown below.

Suitable Ways to Add Comma in Excel to Concatenate Cells

  • After that, copy and paste the following VBA code in your module as shown below.
Function CONCATENATEMANY(Rf As Range, Sep As String) As String
Dim xCell As Range
Dim xConcate As String
For Each xCell In Rf
xConcate = xConcate & xCell.Value & Sep
Next xCell
CONCATENATEMANY = Left(xConcate, Len(xConcate) - 1)
End Function

Easy Ways to Add Comma in Excel to Concatenate Cells

  • Furthermore, press Ctrl + S to save the VBA Code in your workbook.
  • Hence, it will open the Save As window like the image below.
  • Next, type the file name as you want and click on the drop-down of the Save as type as shown below.

Suitable Ways to Add Comma in Excel to Concatenate Cells

  • Afterward, select the Excel Macro-Enabled Workbook (*.xlsm) from the drop-down menu as your workbook contains VBA code.

  • Now, click on the Save option.

  • As a result, your workbook will save the VBA code and a user-defined function CONCATENATEMANY will be added.
  • Now, go to your workbook.
  • Then, select the merged cell (B8:E8) and type the following formula:
=CONCATENATEMANY(B5:E5,",")
  • After that, press Enter.
  • Hence, it will add commas between all the data from B5 to E5 in the Concatenated cell as shown below.

🔎 VBA Code Breakdown

Dim xCell As Range is declaring a variable cell as range value.

Dim xConcate As String is declaring a variable Concatenate as a string.

xConcate = xConcate & xCell.Value & Sep is the command to join the cell value with a separator.

CONCATENATEMANY = Left(xConcate, Len(xConcate) – 1) is the command to concatenate the last concatenated cells.

Read More: How to Add Thousand Separator in Excel Formula


Things to Remember

  • The CONCAT and TEXTJOIN formula only works for Microsoft Office 365. If you have an older version of Microsoft Excel, you won’t be able to apply these 2 The CONCAT function is the latest version of CONCATENATE function of Excel.
  • If you prefer coding applications to solve real-life problems, then the VBA Code approach will be a better option for you in this regard.

Conclusion

Hence, follow the above-described methods. Thus, you can easily learn how to add comma in excel to concatenate cells. Hope this will be helpful. Follow the ExcelDemy website for more articles like this. Don’t forget to drop your comments, suggestions, or queries in the comment section below.


Related Articles

Md. Asaduzzaman

Md. Asaduzzaman

Hello! I am Md. Asaduzzaman. Currently, I am working as an Excel and VBA Content Developer and I will be posting my articles related to this here. I graduated from Bangladesh University of Science and Technology(BUET) in 2022. I completed my BSc in Naval Architecture and Marine Engineering. I like to solve real-life problems in Microsoft Excel and share the solutions through articles. I post here regularly. Hope you find the articles helpful.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo