Nuraida Kashmin
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.
Practice Workbook
Download the following workbook and exercise.
4 Quick Methods to Combine Rows into One Cell
1. Use CONCATENATE and TRANSPOSE Functions to Merge Multiple Cells
With the combination of CONCATENATE and TRANSPOSE functions, we can easily combine rows into one cell. Let’s think I have a dataset and need to merge the cells on Cell C4.
STEP 1:
- Select Cell C4.
- Now write the formula:
=TRANSPOSE(B4:B6)
- Then hit Enter and press F9.
- In the Formula bar ={“221B”,”Baker”,”Street”} is displaying.
STEP 2:
- After that, in the formula bar take off the curly brackets and upgrade the formula:
=CONCATENATE("221B,"," Baker"," Street")
- To find the result, hit Enter.
2. Apply Excel Fill Justify Command to Unify Rows into One Cell
Numerical values or formulas can’t be merged through the Fill Justify feature. Only text values can. Here we have some data to unify the rows into one cell.
STEPS:
- At first, select the required cells.
- Go to the Home tab.
- Here from the Editing group select Fill > Justify.
- Now Click on that and we will see the result.
- Widen the cell if it’s needed.
➧ NOTE: Justify feature won’t work if there is any blank cell. Only single-column cells can merge by using this feature.
3. Insert TEXTJOIN Function to Combine Rows
To merge the text TEXTJOIN Function is also used also but this function is available from 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.
STEPS:
- Select the Cell E4.
- Write the formula:
=TEXTJOIN(", ",TRUE,B5:B8)➧ 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,
- Press Enter and we will see the result.
4. Combine Rows into One Cell with Ampersand in Excel
Without having to see the function, Ampersand helps to join the text rows. From the bellow dataset, we are going to see the Seller’s total products in the last row of the table at Cell C9.
STEPS:
- Select the Cell C5.
- At first write
=B5and hit Enter.
- Then select the Cell B6 and write
=C5&", "&B6
- Now use Fill Handle to autofill Column C and in the last row, you will see the result.
- Lastly, increase the column width of the cells and we can see the result is displayed on the worksheet
.
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. Feel free to ask anything or suggest any new methods.
