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.
How to Merge Rows with Comma in Excel: 4 Methods
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 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 the 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
Read More: How to Merge Rows in Excel Based on Criteria
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.
Read More: How to Merge Rows and Columns in Excel
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.
Read More: How to Merge Rows in Excel without losing Data
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.
Download Practice Book
Download the practice book here.
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