How to Combine Multiple Rows into One Cell in Excel

Sometimes we need to show the multiple rows data into one cell to mean something or to create a new column. In this article, I’m going to describe different methods of combining multiple rows into one cell.

To make it more understandable, I’m using a sample datasheet that contains two columns. The columns are First Name and Favourite Fruits.

Working Sample Datasheet

Sample Workbook to Practice:

Combine Multiple Rows in One Cell in Excel

1. Using the Ampersand Symbol (&)

In your datasheet, first, select the cell where you want to keep multiple rows then select the first cell you want to combine. After selecting the cell type Ampersand symbol (&) with a double-quote (“ ”). Now select the cell you want to combine with and finally press Enter. You can combine multiple rows in that way.

I selected the D4 cell to keep the combined rows and selected the following cells I want to put in one cell.

The Formula is =C4&" "&C5&" "&C6&" "&C7

Using the Ampersand Symbol to combine multiple rows in one cell

If you want to separate your rows content using comma (,), space, or any character, insert those marks in between the space of double quote (“ ”). I am showing you using comma (,).with character (and).

The Formula is =C4&" "&C5&" ,"&C6&" and"&C7

Using the Ampersand Symbol to combine multiple rows in one cell

Read more: How to Combine Rows into One Cell in Excel

2. Using the CONCAT Function 

First, select the cell where you want to put the data combining multiple rows then use the CONCAT or CONCATENATE function. These two functions do the same work.

Syntax of the CONCAT function

CONCAT(text1, [text2],…)

I’m using the CONCAT function. First select cell D4 to put combined value then type =CONCAT, and select row (C5, C6, C7).

If you want to put space, comma, or other text use the double quote (“ ”). Here used comma (,) with character (and) in the double quote (“ ”).

The Formula is =CONCAT(C4,", ",C5,", ",C6," and ",C7)

Using CONCAT function

3. Using the CONCATENATE and TRANSPOSE Functions

Here I will use TRANSPOSE within the CONCATENATE function. The TRANSPOSE function will change the layout of the data and CONCATENATE will combine the data.

Syntax of TRANSPOSE and CONCATENATE function is

TRANSPOSE(array)

CONCATENATE(text1, [text2], ...)

First, select the cell where you want to put your combined multiple row data then use the TRANSPOSE function first.

The Formula is =TRANSPOSE(C4:C7)

Using TRANSPONSE and CONCATENATE to combine multiple rows in one cell in Excel

Now press the F9 key. It will show the row values within curly braces.

Using TRANSPONSE and CONCATENATE to combine multiple rows in one cell in Excel

Now remove the curly braces and use the CONCATENATE function. It will combine all selected rows without space.

The Formula is =CONCATENATE("I Like","Apple","Orange","Cheery")

Using TRANSPONSE and CONCATENATE

To make multiple rows values clear using comma (,) character (and ) as a separator within double quote (“ ”).

The Formula is =CONCATENATE("I Like"," ","Apple"," ","Orange"," and","Cheery")

Using TRANSPONSE and CONCATENATE to combine multiple rows in one cell in Excel

4. Using the TEXTJOIN Function

Here we will use the TEXTJOIN function to combine multiple rows into one cell.

Syntax of TEXTJOIN function is

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

A delimiter is the text separator such as comma, space, character.

Ignore_empty will use TRUE and FALSE where TRUE will ignore the empty value and FALSE will include the empty values.

Texts will join up to 252 strings.

First, select the cell where you want to place the combined value then type the TEXTJOIN function and give the range. Here I selected the range (B4:B7)

The Formula is =TEXTJOIN(",",TRUE,B4:B7)

Using TEXTJOIN function to combine multiple rows into one cell

You also can select the rows one by one with a separator comma (,).

The Formula is =TEXTJOIN(",",TRUE,B4,B5,B6,B7)

Using TEXTJOIN Function

5. Using Formula Bar

You can copy the values of the row then paste them on the Notepad. From the Notepad copy the rows and then paste them in Formula Bar then click Enter. It will paste all selected values in one Cell. We need to copy the value from the sheet to Notepad because an Excel sheet copies cell by cell.

First, copy the value to the Notepad and again copy the values from the Notepad.

Using Formula Bar to Combine Multiple Rows into One Cell

Now keep the cursor in Formula Bar and click the right side of the mouse. From here paste the copied rows.

Using Formula Bar

After clicking on Paste then press ENTER. It will show multiple rows in one cell.

Using Formula Bar

Conclusion

In this article, I have discussed multiple ways of combining multiple rows. I hope it will be helpful for you. If you have any kinds of suggestions, ideas, drawbacks then please feel free to comment down below.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo