If you’re looking for some easy & handy ways to concatenate or combine multiple cells into a single one, then this article should help you the most with a number of basic Excel functions here.
Download Practice Worksheet
You can download our practice workbook which we’ve used to prepare this article.
8 Useful Methods You Should Use to Concatenate Multiple Cells in Excel
To concatenate multiple cells in Excel, there are several options available to meet your requirements. I hope these basic functions & formulas will meet all your needs.
1. Use CONCATENATE or CONCAT Function
Suppose, you have someone’s ID, his first & last names in a datasheet. You want to combine them into a single cell. How will you do it?
⇒ Select D2 Cell
=CONCATENATE(A2, " ", B2, " ", C2)
You’ll get the result at once.
So what’s happening here?
With the CONCATENATE function here you’re joining Cell A2, B2 & C3 together.
You are adding separators among them by using Spaces between Quotations (” “) & Commas (,).
Note: You can type CONCAT instead of CONCATENATE too in the function bar.
2. Insert Ampersand (&) Operator to Concatenate Multiple Cells
You can use the Ampersand (&) operator too to concatenate multiple cells.
⇒ In Cell D2, type
=A2& " " &B2& " " &C2
⇒ Press Enter.
By using Ampersands between two cells, you’re adding these cells together here.
3. Apply Excel ‘Merge & Center’ Command to Join Multiple Cells
Now you have a full name of a person in Cell A1.
When you select the A1 Cell, it’ll show the name is just behind the border between two cells which looks odd, right?
So you have to apply the Merge & Center command here.
⇒ First of all, select Cells A1 & B1 where the full name needs to be shown properly by merging the cells together.
⇒ Select Cell A1.
⇒ Press the Shift key & hold it.
⇒ Without releasing the Shift key, select Cell B1 now.
⇒ Now under the Home tab, move to the Alignment group of commands.
⇒ Click on Merge & Center command.
And here’s the result you’re seeing now.
The A1 & B1 Cells have been merged into one single cell now and the full name is now being displayed at the center of the cell.
4. Insert Line Break & Other ASCII Codes to Concatenate in Excel
If you want to add a line break then you have to insert an ASCII code named CHAR(10).
So here what we’re going to do is in Cell D2, the ID no. will be at the top & the full name under it.
⇒ Select Cell D2 & type
=A2& CHAR(10) &B2& " " &C2
⇒ Press Enter
⇒ Don’t forget to turn on the Wrap Text command from the Alignment group of commands under the Home tab.
So, here’s our result in Cell D2 according to the requirements.
5. Concatenate Columns by Using Excel Fill-Down Command
Let’s think about some columns to be combined.
In our datasheet below, you’re seeing 3 columns including some persons’ IDs, their first names & last names.
You need to combine each one’s ID & full name in the 4th column.
⇒ Tap on Cell D2 & concatenate the Cells A2, B2 & C2 by following the 1st method in this article.
⇒ Point your mouse cursor onto the right bottom corner of the Cell D2 & a Plus ‘+’ symbol you’ll find there.
⇒ Select the ‘+’ symbol, hold & drag it to the Cell D11.
⇒ Release the mouse button & you’ll get the whole data you need.
6. Use TRANSPOSE Function to Concatenate a Large Range of Cells
We can use both CONCATENATE & TRANSPOSE functions together to combine a range of cells from a column.
Here you’re seeing some words in Column A which need to be combined to make an appropriate sentence.
⇒ Select any cell outside the cells containing the range of words & type enable TRANSPOSE function.
⇒ Choose the range of cells (A2 : A8)
⇒ Press Enter & you’ll find all the words in a horizontal array.
Now you need to combine these words together to make a sentence.
⇒ Go to the Function Bar.
⇒ Add the CONCATENATE function over the TRANSPOSE function and before closing the bracket type & ” “. This will insert the spaces among all words later.
⇒ Press F9, not Enter.
This process will convert all the cells into text functions at once.
⇒ Inside the CONCATENATE function, now you’ll see two types of brackets, remove the curly ones.
⇒ Press Enter & you’ll find the whole range of cells (A2 : A8) into a concatenated one.
7. Use TEXTJOIN Function in Excel 365
If you’re using MS Office365 then you’ll find this TEXTJOIN function which will meet your requirements more precisely.
⇒ Select an empty cell & type
=TEXTJOIN(" ",TRUE, A2:A8)
Here inside the parenthesis,
“ “ means you’re adding spaces among all words.
TRUE denotes that the function will skip empty cells if found in your range of cells.
A2:A8 is the range of cells you want to concatenate.
Press Enter & you’re done.
You’ve just got your desired result.
8. Concatenate Multiple Cells with ‘Fill Justify’ Command
You can use the ‘Fill Justify’ command also to combine or concatenate all the cells more quickly.
But to follow this method, first of all, you have to increase the width of Column A by dragging the border(between the Column Names- A & B) rightward.
⇒ Select Editing group of command under Home ribbon or tab.
⇒ From the Fill drop-down, choose the Justify command.
In the picture below, you’ll find the result.
Probably this is the fastest method to concatenate a range of cells together.
But this method has a limitation.
It cannot combine the cells containing numbers, it’s effective for concatenating text strings.
So here are all of these basic methods you can find convenient to use. You can let me know your valuable opinions & thoughts in the comment box. I’ll catch up with you soon!