How to Combine Rows into One Cell in Excel (6 Easy Methods)

In this article, I am going to show you how Excel combines rows into one cell. Fortunately, Microsoft Excel provides us with some useful functions to combine rows into one cell with no loss of data. Today we are going to know some of them.

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

6 Quick Methods to Combine Rows into One Cell

In this section, I am going to show you 6 simple and quick methods to combine rows into one cell in Excel. Let’s explore the methods one by one.

1. Use of CONCAT Function to Combine Rows into One Cell

With the combination of the CONCAT function, we can easily combine rows into one cell. Let’s think I have a dataset in the 3 rows (B4:B6) and need to merge the cells on Cell C4.

To combine those rows into a single cell using the CONCAT function, follow the steps below.

Steps:

• First, select cell C4 and write down the following formula and click Enter.

• As a result, you will see that the words on those rows have been combined in cell C4.

• Now, if you are using an older version of Microsoft Excel, you may not find the CONCAT function. Thus, you can use the older alternative, the CONCATENATE function. This function also yields the same result.
• So, the formula when using the CONCATENATE function will be

In this way, we can combine rows easily into one single cell.

2. Application of Excel Fill Justify Command to Unify Rows into One Cell

In this method, we will utilize the Fill Justify Command to combine rows into one cell. Before starting we should remember that numerical values or formulas can’t be merged through the Fill Justify feature. We can only use this feature for the text values. Here, we have some data to unify the rows into one cell.

Let’s see how the Justify feature unifies those rows into one cell.

Steps:

• First, select the required cells. Here, I have selected cells B4 to B7.  Then, in the Home tab, go to the Editing group and select the Fill icon. Then from the drop-down menu, choose Justify. As a result, you will see that the rows have been merged into one single cell.

• Here, before using the feature, you must widen the rows enough to fit all the words in those rows. Otherwise,  the words will be combined into multiple cells, not one single cell.

➧ NOTE: The Justify feature won’t work if there is any blank cell. Only single-column cells can merge by using this feature.

3. Insertion of TEXTJOIN Function to Combine Rows

To merge text of multiple rows into a single cell, we can also use TEXTJOIN Function. Though this function is available in Excel 365 version only. It’s very easy and simple to understand. Here we have a list of products for the customer ‘Rob’. We have to combine them and show the result in Cell E4.

Now, follow the steps below to join and display all the texts from rows B5 to B8 in cell E5.

Steps:

• First, select cell E4.
• Now, write down the formula:

➧ NOTE: In TEXTJOIN Formula, we need to specify the delimiter. The next argument is to accept or ignore the empty cell. Finally, we have to select the range of data wanted to combine,

• Now, press Enter and we will see the result.

This is how easily we can combine rows into one cell using the TEXTJOIN function.

4. Combining Rows Using Ampersand Operator

If you don’t want to use any function, you can use the Ampersand operator (&) to join text and unify texts from multiple rows and display them in a single cell. Below, I have taken the same data set as in the 3rd method. Now follow the steps below to unify the rows.

Steps:

• Select the Cell C5.
• Now, write down the following formula.

• Finally, hit Enter. As a result, you will see the same result as in the 3rd method.

In this way, we can combine rows using the Ampersand operator.

5. Combining Rows Using Merge & Center Tool from Excel Toolbar (Losing Data)

We can also try to use the Merge & Center tool from Excel Toolbar. Here we’ve got a data set with the names of some Authors and their Books available in a bookshop called Kingfisher Bookstore.

Our objective today is to combine all the books of each writer into one cell.To use Merge & Center tool, follow the steps below.

Steps:

• Select the cells that you want to merge and go to the Home tab and select Merge & Center.Here I have selected the books of the first author, Charles Dickens.

• Consequently, there will appear a prompt box telling you that only the value from the upper left will remain. Click OK.

• You will find the rows have merged, but they contain only the value from the first cell. A Tale of Two Cities in this example.

This is not exactly what we want. Therefore, the Merge & Center tool doesn’t come much helpful here.

6. Combining Rows Using a VBA Macro (Without Losing Data)

Now we will develop a Macro using VBA code that will fulfill all our expectations.

We will develop two Macros for two different cases.

Click here to see how to save and run Macros in Excel.

Case 1: Different Classes Separated Specifically

First of all, we consider the case where different classes of data are separated specifically, like this:

For this type of data set,  follow the steps below.

Steps:

• First of all, press Alt + F11 and go to the VBA window. Then insert this code in a new module:

Sub Merging_Rows()
Dim out As Variant
out = ""
Dim start As Variant
start = 1
Dim ending As Variant
ending = 1
Dim i As Variant
Dim j As Variant
For i = 2 To Selection.Rows.Count + 1
    If Selection(i, 1) <> "" Or i = Selection.Rows.Count + 1 Then
        ending = i - 1
        For j = start To ending
            If j = ending Then
                out = out + Range(Selection(j, 2).Address).Value
            Else:
                out = out + Range(Selection(j, 2).Address).Value + vbNewLine
            End If
        Next j
        Range(Selection(start, 2).Address) = out
        Range(Selection(start, 1).Address + ":" + Selection(ending, 1).Address).Merge Across:=False
        Range(Selection(start, 2).Address + ":" + Selection(ending, 2).Address).Merge Across:=False
        start = i
        out = ""
    End If
Next i
End Sub

• Then, save the file as Excel Macro-Enabled Workbook.
• After that, come back to your worksheet and select the whole data set (Without the Column Headers) that you want to combine.

• Then press Alt + F8 on your keyboard. The Macro dialogue box will open. Select the Macro Merging_Rows and click on Run.

• You will get a few prompt boxes telling you that only the value from the upper left cell will remain. Ignore those. Click OK each time.

• You will find the rows in your data set have been combined beautifully like this.

Case 2: Different Classes Mixed Randomly

Now consider in your data set, different classes are not separated specifically, rather they are mixed randomly, like this:

To combine rows into one cell in this case, follow the steps below.

Steps:

• First, open the VBA window by pressing Alt + F11 and enter this code in a new module:

Code:

Sub Merging_Rows2()
Dim out As Variant
out = ""
Dim start As Variant
start = 1
Dim ending As Variant
ending = 1
Dim i As Variant
Dim j As Variant
For i = 1 To Selection.Rows.Count
    If Selection(i, 1) <> Selection(i + 1, 1) Then
        ending = i
        For j = start To ending
            out = out + vbNewLine + Range(Selection(j, 2).Address).Value
        Next j
        Range(Selection(start, 2).Address) = out
        Range(Selection(start, 1).Address + ":" + Selection(ending, 1).Address).Merge Across:=False
        Range(Selection(start, 2).Address + ":" + Selection(ending, 2).Address).Merge Across:=False
        start = i + 1
        out = ""
    End If
Next i
End Sub

• After that, save the file as Excel Macro-Enabled Workbook.
• Then, come back to your worksheet. Select the whole data set (Including the Column Headers) and go to the Home>Sort & Filter tool in Excel Toolbar.

• Now, select Custom Sort from the Sort & Filter drop-down menu. Consequently, the Sort dialogue box will open.
• Now, under the Column option from the Sort by menu, select Author (The first column of your data set)
• Then, under the Sort On option, select Cell Values.
• Lastly, under the Order option, select A to Z or Z to A.

• Finally, click on OK. As a result, your data set will be sorted according to the ascending or descending order of the Authors (The leftmost column).

• Now select this data set (Without the Column Headers) and run the Macro Merging_Rows2 in the same way as described above.

• Consequently, you will find similar rows of the data set combined into one cell like this.

Thus, you can combine rows into one cell in Excel without losing data.

Things to Remember

• The Justify feature won’t work if there is any blank cell. Only single-column cells can merge by using this feature.

Conclusion

By using these methods, one can quickly combine rows into one cell. There is a practice workbook added. Go ahead and give it a try.  Moreover, feel free to ask anything or suggest any new methods.

Related Readings

• How to Combine Rows with Same ID in Excel (3 Quick Methods)
• Combine Multiple Rows into One Cell in Excel
• How to Transpose Array in Excel (3 Simple Ways)
• Excel Transpose Multiple Rows in Group to Columns
• How to Move Columns in Excel (5 Quick Methods)
• How to Concatenate String and Integer using VBA