5 Easy Methods to Combine Rows into One Cell in Excel

There may be times when you need to use Excel to combine rows into one cell. For example, you may need to use an Excel formula or VBA for this purpose. Today, we’ll show you how to use Excel to combine rows into one cell with examples.


📁 Download Excel File

To prepare this article, we used an Excel workbook, which is available for download. Additionally, you can edit and customize the data you enter while viewing the results.


Learn to Combine Rows into One Cell Using These 5 Easy Methods

Sometimes, you need to combine rows into one cell in Excel for various reasons. We hope that the different steps we’ve given will make it easy for you to do the task. Here, I explain how to combine data from rows into one cell in Excel without losing any data.

Method 1

1. Implementing TEXTJOIN Function

The TEXTJOIN Function can be used to merge text though it is only available in Excel 365. The TEXTJOIN function combines text from different rows with a specified delimiter. It’s simple and easy to understand. Here is a list of states Tom wanted to visit. We are going to combine them in one cell.

excel combine rows into one cell

⬇️⬇️ STEPS ⬇️⬇️

  • Let’s select cell E6.
  • Then we put the following formula:
=TEXTJOIN(“, “,TRUE,B6:B9)

excel combine rows into one cell with TEXTJOIN fynction

  • Then we press Enter to find the result.

📕 Read More: 7 Ways to Concatenate Multiple Cells with Space in Excel

Method 2

2. Using Justify Command

Now we are going to combine text values only with Fill Justify. We should remember that we cannot do that for numerical values with Fill Justify.

⬇️⬇️ STEPS ⬇️⬇️

  • First, select the cells.

  • Then we got the Home tab. From there we go to the Editing group to select Justify from Fill.

excel combine rows into one cell with JUSTIFY

  • The result will look like this.

  • Finally, we merged the cells for a better looking.

Using Justify Command

📕 Read More: 5 Easy Ways to Add Comma in Excel to Concatenate Cells

Method 3

3. Utilizing TRANSPOSE and CONCATENATE Function

Now we can easily combine rows into one cell by using the CONCATENATE and TRANSPOSE functions together. Here the CONCATENATE function joins two or more strings and TRANSPOSE rotates data from rows to columns or columns to rows. Here we have a dataset of the first names, middle names, and last names of a famous inventor. We are going to combine the names in one cell.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we select cell C6.
  • Then we put the following formula:
=TRANSPOSE(B6:B8)

  • Then we press F9 and then Enter.
  • Now we modify the formula like below:
=CONCATENATE(“Alexander”,” Graham”,” Bell”)

excel combine rows into one cell with CONCATENATE function

  • Now we press Enter to find the result. We merged the cells for better looking.

Using CONCATTNATE function

📕 Read More: 6 Ways to Concatenate with Carriage Return in Excel Formula

Method 4

4. Use of Ampersand

We can use Ampersand to combine rows into one cell without using any formula. Here we are going to combine the student IDs in one cell.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we select cell C6 and type:
=B5

  • Then in the following cell, we insert:
=C6&”, “&B7

  • Now we drag down the formula to the last cell.

  • This is the final result.

excel combine rows into one cell with AMPERSAND function

📕 Read More: 2 Ways to Bold Text in Concatenate Formula in Excel

Method 5

5. Applying CONCATENATE Function

Now we can easily combine rows into one cell by only using the CONCATENATE functions. Here the CONCATENATE function joins two or more strings. Here we have a dataset of the first name, middle name, and last name of a famous inventor. We are going to combine the names in one cell.

⬇️⬇️ STEPS ⬇️⬇️

  • First, we select cell E5.
  • Then we put the following formula:
=CONCATENATE(B6,” “,C6,” “,D6)

  • Now we press Enter then we drag down the formula.

  • This will be the final result.

excel combine rows into one cell with CONCATENATE function with only CONCATNATE function


📄  Important Notes

🖊️  In Excel, the ribbon is used to access various commands. You can change a lot of things in the options dialogue window, like the ribbon, formulas, proofing, saving, and so on. So the appearance of worksheets on your device could be different from ours.

🖊️  A practice workbook is given so that you can practice yourself.

🖊️  All these exercises and tutorials are done on Microsoft Office 365. Some functions may be unavailable in previous versions.

🖊️  At the side of each Excel worksheet file, there is a space where they can practice.


📝  Takeaways from This Article

📌  The reader will be able to use Excel to combine rows into one cell with various methods.

📌  Readers can apply TEXTJOIN to combine rows into one cell.

📌  Readers can insert Justify Command to combine rows into one cell.

📌  They can also combine multiple cells by using the CONCATENATE and TRANSPOSE functions.

📌  Viewers can also combine multiple cells by using the Merge cell add ins.

📌  The readers will get familiar with combining rows into one cell in Excel by using the Ampersand.

📌  Finally, readers can combine multiple cells by using different add ins.


Conclusion

That concludes today’s session. These are the methods for using Excel to combine rows into one cell in Excel. We are hopeful that this article will be extremely beneficial to you. Don’t forget to leave your thoughts and questions in the comments section and explore our website, ExcelDen, the best Excel solutions provider.


Related Articles

(Visited 66 times, 1 visits today)
Tanzim

Tanzim

My name's Tanzim. My articles are meant to help you learn more about how Microsoft Excel works and how to use its different features. I enjoy gaming and hope that some of my knowledge will help me provide you with some interesting articles despite some tiresome technical terms.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo