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**.

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.

- 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 ID**s**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**.

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.

- 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 ID**s**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.

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.

- 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 ID**s 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**).

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.

**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.

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.

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

- 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
```

- 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.

- 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.