Shamima
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.
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
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
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)
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)
Now press the F9 key. It will show the row values within curly braces.
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")
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")
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)
You also can select the rows one by one with a separator comma (,).
The Formula is =TEXTJOIN(",",TRUE,B4,B5,B6,B7)
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.
Now keep the cursor in Formula Bar and click the right side of the mouse. From here paste the copied rows.
After clicking on Paste then press ENTER. It will show multiple rows in one cell.
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.
