Tanzim
There may be times when you need to combine multiple rows in one cell in Excel. 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 multiple rows in 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 Multiple Rows in One Cell in Excel Using These 6 Easy Methods
Sometimes, you need to combine multiple rows in 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 single cell in Excel without losing any data.
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 into one single cell.
⬇️⬇️ STEPS ⬇️⬇️
- Let’s select cell E6.
- Then we put the following formula.
- Then we press Enter to find result.
Read More: 6 Suitable Ways to Convert Multiple Rows to Single Row in Excel
2. Use of Fill Justify Feature
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, we make a new column ”Combined” adjacent to the first column and copy the previous column’s data to the next one.
- Then we select the cells.
- Then we go to Home tab. From there we go to Editing group to select Justify from Fill.
- The result will look like this.
- Finally, we merged the cells for a better looking.
Read More: 3 Ways to Convert Multiple Rows to Single Column in Excel
3. Utilizing CONCATENATE and TRANSPOSE Functions
Now we can easily combine rows in 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 see 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 C6.
- Then we put the following formula.
- Then we press F9 and then Enter.
- Now we modify the formula like below.
=CONCATENATE(“Alexander”,” Graham”,” Bell”)
- Now we press Enter to find the result.
- We merged the cells for a better look.
Read More: 3 Suitable Ways to Combine Rows in Excel with Same ID
4. Using the Ampersand
We can use Ampersand to combine rows into one cell without using any formula. Here we are now going to combine the student in one cell.
⬇️⬇️ STEPS ⬇️⬇️
- First we select cell C6 and type:
- Then in the following cell, we insert.
- Now we drag down the formula to the last cell.
- This is the final result.
Read More: 4 Quick Ways to Merge Rows with Same Value in Excel
5. Applying CONCATENATE Function
Now we can easily combine rows in one single 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 now going to combine the names in one cell.
⬇️⬇️ STEPS ⬇️⬇️
- First, we select cell E5.
- Then we put the following formula.
- Now we press Enter then we drag down the formula.
- This will be the final result.
6. Employing VBA
Now we will use VBA to combine multiple rows. Before talking about the method, first, we have enabled the Developer Tab on our Ribbons. You can follow the link to see the process of enabling the Developer Tab by clicking on it here. Let’s follow the steps below:
⬇️⬇️ STEPS ⬇️⬇️
- Let’s press Alt+F11 to bring up the VBA You can also do this by going to the Developer tab and clicking on Visual Basic.
- Then we choose Module from Insert from the menu. This will bring up the window for the VBA Module. Here is where we will put our code.
- Now we type the code below.
Sub MultRowComb()
Dim State As String
State = ""
For i = 6 To 9 State = State & " " & Cells(i, 2)
Next
Range("E6").Value = State
End Sub- Now we save and run the code.
- Now we will see the outcome.
📄 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 space where they can practice.
📝 Takeaways from This Article
📌 The reader will be able to use Excel to combine rows into one single cell with various methods.
📌 Readers can apply TEXTJOIN to combine rows in one cell.
📌 Readers can insert Justify Command to join rows in one cell.
📌 They can also combine multiple cells by using the CONCATENATE and TRANSPOSE functions.
📌 Viewers can also add multiple cells by using the Merge cell add-ins.
📌 The readers will get familiar with combining rows into one single cell in Excel by using the Ampersand.
📌 Finally, readers can combine multiple cells by using the different add-ins and VBA.
Conclusion
That concludes today’s session. These are the methods for using Excel to calculate the bacterial growth rate. 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.
