How to Merge Rows with Comma in Excel (4 Quick Methods)

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.

Apply Different Functions to Merge Rows with Comma in Excel


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)

Apply Different Functions to Merge Rows with Comma in Excel

  • After that, hit Enter to see the result.

Apply Different Functions to Merge Rows with Comma in Excel

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

Apply Different Functions to Merge Rows with Comma in Excel

  • Next, select an empty cell and type the formula there:
=CONCATENATE(E5,",",E6,",",E7,",",E8)

Apply Different Functions to Merge Rows with Comma in Excel

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

Apply Different Functions to Merge Rows with Comma in Excel


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)

Combine Rows with Comma Using Excel TEXTJOIN Function

  • Thirdly, press Enter to see the result.

Combine Rows with Comma Using Excel TEXTJOIN Function

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

Combine Rows with Comma Using Excel TEXTJOIN Function

  • Now, select Cell B10 and type the formula there:
=TEXTJOIN(",",TRUE,E5:E8)

Combine Rows with Comma Using Excel TEXTJOIN Function

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

Combine Rows with Comma Using Excel TEXTJOIN Function


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)

Merge Rows with Comma Using CONCAT Function

  • In the following, hit Enter to see the result.

Merge Rows with Comma Using CONCAT Function

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

Merge Rows with Comma Using CONCAT Function

  • Again, select an empty cell to type the formula below:
=CONCAT(E5,",",E6,",",E7,",",E8)

Merge Rows with Comma Using CONCAT Function

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

Merge Rows with Comma Using CONCAT Function


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.

Use TRANSPOSE Function to Combine Rows in Excel with Comma

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&","))

Use TRANSPOSE Function to Combine Rows in Excel with Comma


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

Merge Rows in Excel with Comma Using ‘&’ Operator

  • Thirdly, press Enter.

Merge Rows in Excel with Comma Using ‘&’ Operator

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

Merge Rows in Excel with Comma Using ‘&’ Operator

  • Next, select any empty cell and type the formula:
=E5&","&E6&","&E7&","&E8

Merge Rows in Excel with Comma Using ‘&’ Operator

  • Finally, hit Enter to see results like the picture below

Merge Rows in Excel with Comma Using ‘&’ Operator


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.

Excel Flash Fill to Merge Rows with Comma

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

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

2 Comments
  1. 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

Leave a reply

ExcelDemy
Logo