How to Combine Rows into One Cell in Excel

Today I will be showing how to combine rows into one cell in Excel.

While working in Excel, trying to combine rows or columns is one of the most common tasks that we perform. Today I will be showing how you can combine rows into one cell in Excel.

Download Practice Workbook

How to Combine Rows into One Cell in Excel

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.

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

We can try to use the Merge & Center tool from Excel Toolbar. To use that:

• Select the cells that you want to merge and go to the Home>Merge & Center tool in Excel Toolbar.

Here I have selected the books of the first author, Charles Dickens.

• Click on the Drop-Down menu. You will find a few options. Select Merge & Center.

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

We have to search for alternative ways.

Read More: How to Merge Rows in Excel (2 Easy Methods)

2. Combining Rows Using Formula (Without Losing Data)

Option 1: Using the TEXTJOIN Function

We can combine the values from multiple rows into one single row using the TEXTJOIN function of Excel.

Note: The TEXTJOIN function is available only in Office 365 and Excel 2019.

The Syntax of the TEXTJOIN function is:

The formula to combine all the books of Charles Dickens into one cell will be:

• Here “, “ is the delimiter by which we wanted to separate the names of the books. You can use anything you wish as a delimiter.
• The ignore_blank argument has been set to be TRUE. If there had been any blank cell within our given range, that would have been ignored.
• C4:C7 is the range of the books of Charles Dickens that we want to combine. You use what you need.

Now you have to enter this formula for the rest of the authors manually.

For example, for P. B. Shelly, the formula will be:

This method does work but is not quite practical when we have a very large data set in our hands.

Because entering a large number of formulas separately can be quite troublesome.

But this process will come in handy when all the subsets have the same number of rows.

For example, look at this data set. We have the Names of some employees and their scheduled Workdays.

Here, all the employees have the same number of workdays, that is 4 days.

Here you can enter the TEXTJOIN formula for the first employee:

Then copy the formula using Ctrl + C and paste it alongside the rest of the employees using Ctrl + V.

As we have used the relative cell reference, Excel will automatically adjust it while copying and pasting.

For example, if you paste the formula in D8 alongside Natalia Austin, Excel will convert it to:

And the same for all the employees.

Before going to the next section, I want to add one more thing.

If your data set doesn’t have the different classes separated specifically, rather they are mixed randomly, like this data set:

You can use a slightly different process to combine similar rows.

First, use this formula to extract the Names of all the different Authors in a new column:

Here, B4:B27 is the list of my Authors. You use it according to your needs.

Then insert this formula beside the name of the first Author:

• Here C4:C27 is the list of all my Books.
• B4:B27 is the list of my Authors.
• And E4 is the first cell of the Authors.

After inserting the formula in the first cell, drag the Fill Handle to copy the formula to the rest of the cells.

Option 2: Using the CONCATENATE and TRANSPOSE Function

The TEXTJOIN function is only available in Office 365 and Excel 2019.

So those who do not have a subscription can follow this procedure.

We will use a combination of the CONCATENATE and the TRANSPOSE function here.

For combining the books of the first Author, use this formula:

Note: Here C4:C17 is the range of the Books of my first Author, Charles Dickens. You use it according to your needs.

Then repeat the formula manually for all the other Authors.

Like the previous section, if all your subsets have the same number of rows, then you can just copy and paste it into respective cells.

And if they are not, you have to enter the formulas manually.

Read More: Opposite of Concatenate in Excel (4 Options)

Similar Readings:

• How to Add Leading Zeros in Excel by CONCATENATE Operation
• Carriage Return in Excel Formula to Concatenate (6 Examples)
• Concatenate Multiple Cells but Ignore Blanks in Excel (5 Ways)
• Excel INDEX MATCH to Concatenate Multiple Results (With 2 Easy Steps)
• How to Concatenate Rows in Excel (11 Methods)

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

The two methods described above are helpful, yet seem to not fulfill our expectations to the fullest.

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, press Alt + F11 and go to the VBA window. Then insert this code in a new module:

Code:

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

Save the file as Excel Macro-Enabled Workbook.

Then 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 Classed Mixed Randomly

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

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

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.

Select Custom Sort from the Sort & Filter drop-down menu. The Sort dialogue box will open.

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.

Under the Order option, select A to Z or Z to A.

Then click on OK. 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.

You will find similar rows of the data set combined into one cell like this.

Read More: Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)

Conclusion

Using these methods, you can combine rows in Excel to differentiate similar types of data. Do you have any other questions? Feel free to ask us.

Related Articles

• How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)
• Concatenate Two Columns in Excel with Hyphen (9 Quick Ways)
• How to Concatenate and Keep Currency Format in Excel (3 Methods)
• Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)
• How to Concatenate Multiple Cells With Space in Excel (7 Methods)