In this article, we will learn to **merge rows with the comma in Excel.** Unfortunately, there is no method to merge rows directly with each other. Here, we will merge cells of a row in a specific cell first. We will follow the process for all rows and then, we will merge the rows with the comma. So, without further ado, let’s learn to merge rows with the comma in excel.

## Download Practice Book

Download the practice book here.

## 4 Methods to Merge Rows with Comma in Excel

To explain the methods, we will use a dataset that contains information about the **Department **and the **Age **of some employees of a company.

### 1. Apply Different Functions to Merge Rows with Comma in Excel

In the first method, we will use some functions to merge rows with the comma in Excel. We will discuss the process in the sub-method below.

#### 1.1 Excel CONCATENATE Function to Combine Rows with Comma

The **CONCATENATE **function combines cells. We will use **the CONCATENATE function **to combine the cells of a row and then use them to show merged rows. Let’s follow the steps below to learn the technique.

**STEPS:**

- In the first place, create a
**Helper**column. - Secondly, select
**Cell E5**and type the formula below:

`=CONCATENATE(B5," ",C5," ",D5)`

- After that, hit
**Enter**to see the result.

- Now, drag the
**Fill Handle**down to see results in the rest of the cells.

- Next, select an empty cell and type the formula there:

`=CONCATENATE(E5,",",E6,",",E7,",",E8)`

- Finally, press
**Enter**to see results like the picture below.

#### 1.2 Combine Rows with Comma Using Excel TEXTJOIN Function

Here, we will use **the TEXTJOIN function** to merge rows with commas in excel. The **TEXTJOIN **function joins texts of different cells. We will use the previous dataset here. Let’s observe the steps below for more.

**STEPS:**

- Firstly, create a
**Helper**column. - In the second place, select
**Cell E5**and type the formula below:

`=TEXTJOIN(" ",TRUE,B5:D5)`

- Thirdly, press
**Enter**to see the result.

- Next, drag down the
**Fill Handle**to see results in the rest of the cells.

- Now, select
**Cell B10**and type the formula there:

`=TEXTJOIN(",",TRUE,E5:E8)`

- Finally, hit
**Enter**to see results like the picture below.

#### 1.3 Merge Rows with Comma Using CONCAT Function

There is another function that can give us merged rows with the comma in Excel. That is **the CONCAT function**. The **CONCAT **function joins values of different cells in Excel. It is similar to the **CONCATENATE **function. Because the **CONCAT **function was introduced to replace the **CONCATENATE **function. Again, we will use the same dataset. Let’s pay attention to the steps below for more.

**STEPS:**

- In the beginning, you need to create a
**Helper**column. - After that, select
**Cell E5**and type the formula below:

`=CONCAT(B5," ",C5," ",D5)`

- In the following, hit
**Enter**to see the result.

- Now, drag the
**Fill Handle**down to autofill formulas in the rest of the cells.

- Again, select an empty cell to type the formula below:

`=CONCAT(E5,",",E6,",",E7,",",E8)`

- In the end, press
**Enter**to see results like the image below.

#### 1.4 Use TRANSPOSE Function to Combine Rows in Excel with Comma

We can also use **the TRANSPOSE function **to join rows in Excel with the comma. Here, we will use the **CONCATENATE **and **TRANSPOSE **functions together. The **TRANSPOSE **function will convert the values of a column into a row and the **CONCATENATE **function will join them. So, let’s follow the steps below.

**STEPS:**

- First of all, select
**Cell B10**and type the formula below:

`=CONCATENATE(TRANSPOSE(B5:B8&","))`

- After that, hit
**Enter**to see results like the picture below.

Here, the formula will take the first of each row of **Column B** and convert them into a single row with the comma separator.

- Once again, you can use the formula below to show the
**Department**and the**Age**:

`=CONCATENATE(TRANSPOSE(B5:D8&","))`

### 2. Merge Rows in Excel with Comma Using Ampersand (&) Operator

The **Ampersand (&)** operator also helps to merge rows very easily. Again, we will use the same dataset to explain the process. So, without delay, let’s start discussing the steps.

**STEPS:**

- Firstly, create a
**Helper**column to merge the cells of each row. - In the second step, select
**Cell E5**and type the formula:

`=B5&" "&C5&" "&D5`

- Thirdly, press
**Enter**.

- In the following, autofill the formula in the rest of the cells using the
**Fill Handle**.

- Next, select any empty cell and type the formula:

`=E5&","&E6&","&E7&","&E8`

- Finally, hit
**Enter**to see results like the picture below

### 3. Excel Flash Fill to Merge Rows with Comma

In Excel, we can use the **Flash Fill** feature to merge rows with the comma. It fills any series with a pattern very quickly. Here, we will use the same dataset. Now, follow the below steps to know the procedure.

**STEPS:**

- To begin with, select
**Cell E5**and type the values of**Cell B5**,**C5**&**D5**with a space like the below picture.

- Secondly, select the cells that you want to fill like
**Cell E5**.

- After that, go to the
**Home**tab and then, select**Fill**. A drop-down menu will appear. - Select
**Flash Fill**from the drop-down menu.

- After selecting the
**Flash Fill**, you will see results like the picture below.

- Now, select
**Cell B10**and type the formula:

`=CONCAT(E5,",",E6,",",E7,",",E8)`

- Finally, press
**Enter**to see the result.

### 4. Use VBA to Join Rows with Comma in Excel

In the last method, we will create a user-defined function with **VBA **to merge rows with the comma separator. We will use the same dataset again. Let’s follow the steps below to know the process perfectly.

**STEPS:**

- First of all, select
**Cell E5**and type the formula:

`=CONCAT(B5,",",C5,",",D5)`

- In the second place, hit
**Enter**and then, drag the**Fill Handle**down.

- After that, go to the
**Developer**tab and select**Visual Basic**. It will open the**Visual Basic**window.

- Next, select
**Insert**and then**Module**to open the**Module**window. - Now, type the code in the
**Module**window:

```
Function Merge(xWorkRg As Range, Optional Sign As String = ",") As String
Dim xRg As Range
Dim xOutSr As String
For Each xRg In xWorkRg
If xRg.Text <> " " Then
xOutSr = xOutSr & xRg.Text & Sign
End If
Next
Merge = Left(xOutSr, Len(xOutSr) - 1)
End Function
```

- In the following, press
**Ctrl**+**S**to save the code. - After saving the code, close the
**Visual Basic**window. - Then, type the formula in
**Cell B10**:

`=Merge(E5:E8)`

- Finally, hit
**Enter**to see the result.

## Things to Remember

We have used the above methods to merge rows with values in multiple columns. You can also follow the same methods to merge rows that have values in a single column. You just need to type the cell position correctly. Suppose, you want to merge **Cell B5**, **B6 **& **B7**. Then, you need to use the formula below:

`=CONCATENATE(B5,",",B6,",",B7)`

So, in this case, you don’t need any helper columns. Also, if you want to use any other separator, then, just replace the comma with the desired separator in the formula.

## Conclusion

We have demonstrated **4 **methods to **Merge Rows with Comma in Excel**. We have also discussed a user-defined function with **VBA**. Moreover, the practice book is also added at the beginning of the article. You can download it to practice more. Last of all, if you have any queries or suggestions, feel free to ask in the comment section below.

Have any idea about VBA or Formula Without Helper Column ?

Hi BIPLAB,

Thanks for your query. You can build a formula with the nested CONCAT function. In that case, you don’t need any helper column. For the dataset we have discussed in the article, the formula will be:

=CONCAT(CONCAT(B5,” “,C5,” “,D5),”,”, CONCAT(B6,” “,C6,” “,D6),”,”,CONCAT(B7,” “,C7,” “,D7),”,”,CONCAT(B8,” “,C8,” “,D8))

You can also use the VBA code below. To apply this, you need to select the rows of the first column of the dataset and then run the code from the Macro window.

Sub Merge_Rows_with_Comma()

Dim iSelection As Range

Dim iRow As Range

Dim iCell As Range

Dim iStr As String

Application.ScreenUpdating = False

On Error Resume Next

Set iSelection = Intersect(Selection, ActiveSheet.UsedRange)

For Each iRow In iSelection.Rows

For Each iCell In iRow.Cells

iStr = iStr & ” ” & VBA.Trim$(iCell)

Next iCell

iRow.ClearContents

iRow.Cells(1, 1).NumberFormat = “@”

iRow.Cells(1, 1) = Mid(iStr, 2)

iStr = vbNullString

Next iRow

For Each iCell In iSelection

iStr = iStr & “,” & VBA.Trim$(iCell)

Next

With ActiveWindow

.Selection.ClearContents

.Selection(1, 1).NumberFormat = “@”

.Selection(1, 1).Value = Mid(iStr, 2)

End With

End Sub