How to Concatenate Multiple Cells in Excel (8 Quick Approaches)

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?

Using concatenate or concat function

Steps:

⇒ Select D2 Cell

⇒ Type =CONCATENATE(A2, " ", B2, " ", C2)

Using concatenate or concat function

Press Enter.

You’ll get the result at once.

Using concatenate or concat function

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.

Steps:

⇒ In Cell D2, type =A2& " " &B2& " " &C2

⇒ Press Enter.

Concatenate cells by using ampersand

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.

Concatenate cells by using merge and center command

Steps:

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

Concatenate cells by using merge and 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.

Concatenate cells by using merge and center command


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.

Concatenate cells by using line break

Steps:

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

Concatenate cells by using line break


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.

Concatenate cells and columns by choosing fill down

Step 1:

⇒ Tap on Cell D2 & concatenate the Cells A2, B2 & C2 by following the 1st method in this article.

Concatenate cells and columns by choosing fill down

Step 2:

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

Concatenate cells and columns by choosing fill down

Step 3:

⇒ Release the mouse button & you’ll get the whole data you need.

Concatenate cells and columns by choosing fill down


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.

Concatenate cells by using transpose function

Step 1:

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

Concatenate cells by using transpose function

Now you need to combine these words together to make a sentence.

Step 2:

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

Concatenate cells by using transpose function

This process will convert all the cells into text functions at once.

Step 3:

⇒ Inside the CONCATENATE function, now you’ll see two types of brackets, remove the curly ones.

Concatenate cells by using transpose function

Step 4:

⇒ Press Enter & you’ll find the whole range of cells (A2 : A8) into a concatenated one.

Concatenate cells by using transpose function


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.

Steps:

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

Concatenate cells by using textjoint function

Press Enter & you’re done.

You’ve just got your desired result.

Concatenate cells by using textjoint function


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.

Concatenate cells by using fill justify option

Steps:

⇒ Select Editing group of command under Home ribbon or tab.

⇒ From the Fill drop-down, choose the Justify command.

Concatenate cells by using fill justify option

In the picture below, you’ll find the result.

Concatenate cells by using fill justify option

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.


Concluding Words

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!


You May Also Like to Explore

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo