My name is Lee and in this tutorial I’ll show you How to Combine Cells in Excel including multiple cells into one cell.
This means you no longer have to manually copy and paste data bit by bit!
How to Combine Cells in Excel Video
How to Combine Cells in Excel
Let’s have a look at the following example data set – first names, last names, and countries.
We’ll end up having them all written out in one cell, one next to each other, in the rows of the column E.
First, select a target cell that should contain the final combined contents.
Then, click on the option fx – ‘Insert Function’.
A pop-up window will appear where, in the box which says ‘select a category’, you will choose ‘All’.
Look for the function ‘Concatenate’ and confirm with OK.
Concatenate Cells
At this point, Excel will offer you only two boxes at first – Text 1 and Text 2.
Into these boxes, you can type in the text you want to combine.
More boxes are added automatically when you click into the last box.
And if you still need more – don’t worry!
Excel will allow you to concatenate up to two hundred and fifty-five text boxes.
Let’s enter a first name into the ‘Text 1’ box.
We’ll click into the box with ‘Text 1’ and, right away, click on the cell which contains the data we want to use, in our case cell B3.
Now let’s say we want to add a space between the first and the last name.
We’ll click into the box ‘Text 2’ and hit the spacebar to enter a single space.
When we move to the box below (box number 3), Excel will turn this simple space into double quotation marks with the space located between them.
The box with ‘Text 3’ should contain a last name, so click into the box again and then click on the cell C3 which contains the ‘wanted’ bit of information.
It might be very helpful to check this part over here as it displays how concatenated text will look like once done.
If you need to add a comma after the last name, type in a comma into ‘Text 4’ box and it immediately appears in the preview.
The same way you can add a hyphen, colon or any character you need.
The box ‘Text 5’ will be filled in with a country.
To do this, we’ll follow the same steps you’re familiar with now.
Once everything we need has been combined, click on OK.
Bosh!
The text has been linked together without tedious copying and pasting or even retyping!
How to Concatenate Text in the Remaining Rows
In case you need to combine the text in the other rows, too, you don’t have to write out any functions or data again.
Just hover with your mouse over the bottom right corner of the cell that contains the prepared function and drag it down through all the rows in which you want to use the ‘concatenate’ function.
And here’s an important tip before we wrap it all up.
How to Change Concatenated Text Data in the Cell
Once everything has been concatenated and when you click on a cell that contains combined text.
You’ll see that the cell does not contain the text itself, but references to other cells. As well as spaces and commas from the final text included in quotation marks.
Therefore, if you want to change any data in the cell with concatenated text, you’ll need to make changes in the source cells that include this piece of information.
Maybe you’re asking now how the concatenated text can be copied for further work into another cell as a simple text.
Then you will want our tutorial How to Copy and Paste Values Without Formula in Excel.
If you found this tutorial helpful, and if you want to learn more about Excel our complete guide section is the place to go that will help you use Excel like a boss!
Want to learn how functions work in excel then check out my excel functions page where you will learn many of the functions. Or looking for formulas? Well, we have you covered there too with our Excel Formulas page.