How Excel Combine Rows into One Cell (4 Methods)

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.

Use CONCATENATE and TRANSPOSE Functions to Merge Multiple Cells

STEP 1:

  • Select Cell C4.
  • Now write the formula:
=TRANSPOSE(B4:B6)

Use CONCATENATE and TRANSPOSE Functions to Merge Multiple Cells

  • Then hit Enter and press F9.
  • In the Formula bar ={“221B”,”Baker”,”Street”} is displaying.

Use CONCATENATE and TRANSPOSE Functions to Merge Multiple Cells

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.

Apply Excel Fill Justify Command to Unify Rows into One Cell

STEPS:

  • At first, select the required cells.
  • Go to the Home tab.
  • Here from the Editing group select Fill > Justify.Apply Excel Fill Justify Command to Unify Rows into One Cell
  • Now Click on that and we will see the result.

Apply Excel Fill Justify Command to Unify Rows into One Cell

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

Insert TEXTJOIN Function to Combine Rows

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,

Insert TEXTJOIN Function to Combine Rows

  • Press Enter and we will see the result.

Insert TEXTJOIN Function to Combine Rows

 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.

Combine Rows into One Cell with Ampersand in Excel

STEPS:

  • Select the Cell C5.
  • At first write
=B5

and hit Enter.

Combine Rows into One Cell with Ampersand in Excel

  • Then select the Cell B6 and write
=C5&", "&B6

Combine Rows into One Cell with Ampersand in Excel

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


Related Readings

Nuraida Kashmin

Nuraida Kashmin

Hi Everyone! I am Nuraida. Cordially welcome you to my profile. I am a Team Leader of Excel and VBA Content Developer in ExcelDemy. Here I will also post articles related to Microsoft Excel. With a strong educational background in Mechanical Engineering through experimental learning from Rajshahi University of Engineering & Technology. Skilled in Microsoft Word, Creative Writing, Microsoft Excel, Project Management, Support to Organize Different Events, Reporting, Monitoring & Documentation, Online Advocacy and Event Management Related to SAP and Youth Leaders.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo