# How to Combine Cells in Excel (4 Methods + Shortcut)

Get FREE Advanced Excel Exercises with Solutions!

While dealing with Microsoft Excel, we often have to combine values in cells in different situations. MS Excel has numerous ways to join cells, and we will see 4 of them with suitable examples and proper illustrations.

Before starting, let us first introduce the sample dataset for a better understanding of the methods. Where we used the CONCAT function to combine two sets of cell together in Excel.

## A Keyboard Shortcut to Combine Cells

A keyboard shortcut that turns the Auto Flash Fill feature on is discussed here.

Keyboard Shortcut: Ctrl+E

Let’s see!

Steps:

• Type the Full Name Edgar Allan Poe manually.

• Select the range of cells E5:E10.
• Press Ctrl+E.
• There you are! The result is ready.

Note:

• Using this Keyboard shortcut for Flash Fill has an amazing advantage. If you change the pattern in the first Cell E5, Excel will detect the change in pattern and change it accordingly in other cells E6:E10. Suppose you have added a comma after each of the names, Edgar, Allan, Poe. See what happens to other names! 👇

• This method cannot detect any change in text in reference cells.

## 4 Easy Methods to Combine Cells in Excel

Here, we have a list of some people’s names: the First Name within Column B, the Middle Name within Column C, and the Last Name within Column D. We have to join the cell values in those columns and get the output in the Full Name column (E).

We can now start exploring the following methods one by one. Let’s go!

### 1. Apply Excel Function to Combine Cells

#### 1.1 Use the TEXTJOIN Function

Let’s see how it works.

Steps:

• Enter the following formula in Cell E5.
`=TEXTJOIN(" ", TRUE, B5:D5)`

• Drag the fill handle icon.
• See the following screenshot for the result.

🔗 Note:

• With this function, you can use a delimiter, like a space, a comma, or whatever you like. Just place the delimiter within a double quote `""`.
• TRUE/FALSE arguments mean whether to consider (FALSE) or ignore (TRUE) the blank cells in the combined output.

#### 1.2 Utilize the CONCAT Function to Unify Cells in Excel

The CONCAT function replaces the CONCATENATE function in MS 365 version. It can join text from multiple ranges and/or strings. But just as like with the CONCATENATE function it does not provide a delimiter or IgnoreEmpty arguments.

Now let’s see how to combine cells with the CONCAT function.

Steps:

• Type the following formula in Cell D5.
`=CONCAT(B5:C5)`
• Or, type CONCAT( and select the range of cells you want to join, and close the parenthesis.

• Pull the fill handle icon.
• See the result below. 👇

🔗 Comment:

The only usefulness of this function over the CONCATENATE function is it provides the facility of joining one or more ranges of cells. But it does not provide any delimiter.

#### 1.3 Use the CONCATENATE Function in order to Unite Adjacent Cells in Excel

The CONCATENATE function is also a useful tool to combine cell values. Let’s see how it works.

Steps:

• Write the following formula in Cell E5 and press Enter.
`=CONCATENATE(B5, " ", C5, " ", D5)`

• Drag or double-click on the fill handle icon.
• So, the following screenshot shows the result that we’ve got after applying the CONCATENATE function.

Read More: How to Combine Cells Using Excel Formula (6 Methods)

### 2. Use the Flash Fill Feature to Merge Cells in Excel

The Flash Fill is a fantastic feature in MS Excel that can detect the pattern in a column and how the column is being filled down. In this method, we are going to use this amazing Excel tool.

Steps:

• Manually type the Full Name Edgar Allan Poe in Cell E5. MS Excel will sense the pattern you provide here.

• Then notice the flash fill handle in the corner of the cell E5.

• Drag that Fill Handle to cell E10 right clicking the mouse.
• Then release the handle, select Flash Fill.

• You will see that the range of cell now filled with words combining the Name parts altogether.

If Flash Fill Does Not Recognize Pattern:

• If Flash Fill doesn’t seem to work, go to File > Options > Advanced > Editing options > Automatically Flash Fill.

The Drawback of Flash Fill:

• Not applicable when you have a large number of cells along the row.
• A major drawback of this method is it cannot detect any change in text in the reference cells.
• Can not detect any change in pattern.

### 3. Join Cells with the Ampersand (&) Symbol

To apply this method, execute the following steps.

Steps:

• Type the following formula in Cell E5.
`=B5&" "&C5&" "&D5`

• Drag/double-click on the fill handle icon.

• From the following screenshot, we can see that the first, middle, and last names are now joined with a space between two consecutive names.

🔗 Comment:

• This method is a bit tedious when you have to join four, five, six, or more cells side by side. I mean, how many times one can type the & symbol! However, it’s still one of the easiest to use when joining a couple of cells. And it’s available in all versions, new and old, of MS Excel. That is another advantage.
• Using CONCATENATE is easier than using Ampersand because you just have to type the function name once and add the cell references one after another with commas in between.

Read More: How to Combine Two Cells in Excel with a Dash (5 Methods)

### 4. Use Notepad App to Join Cells in Excel

You can also combine values in cells with the help of the Notepad. Let’s see how to do this.

Steps:

• Copy the range of cells E5:E10.

• Open a text file (*.txt) and paste the text.

• Press Ctrl+H.
• Replace with a space (press the space button from the keyboard once).

• Replace All > Cancel.
• Copy the full text again >  Click on Cell E5 > Ctrl+V.
• Our Job is done.

Read More: Shortcut for Merge and Center in Excel (3 Examples)

## How to Remove Unwanted Characters After Combining Cells

Assume that we have a dataset where there are some unwanted characters such as a hyphen or unwanted space just like the below.

• Now, when we apply any of the methods stated above we will have the result like the following screenshot.
• To remove these characters and get a perfect result, we will now improvise a bit the formula we’ve used earlier in this article.
• For example, we will join the TRIM and the SUBSTITUTE functions with the TEXTJOIN function to remove the characters.
Watch the following steps carefully.

Steps:

• Type the following formula in Cell E5:
`=TRIM(SUBSTITUTE(TEXTJOIN(" ", TRUE, B5:D5), "-", ""))`

• Hit Enter.
• Then all the way, Pull the fill handle icon.

• Now, look at the result in the following screenshot!

Read More: How to Combine Cells with Same Value in Excel (3 Easy Ways)

## How to Merge Cells in Excel (Not Cell Data)

We can combine cells with the simple Merge command from the Alignment group of the Home tab.
But this time, only the data in the active cell will sustain. Other cell data will be erased.

Steps:

• Firstly, choose the cells you want to merge, B2:E2.

• After that, go to the Home tab > the Alignment group > Merge & Center.

Or,

• Then, select the cells you want to merge, B2:E2.
• Ctrl +1 > the Alignment tab > Select the following options.

• Finally, press OK.

Read More: Excel Shortcut to Merge Cells (3 Methods + Bonus)

## Concluding Words

I hope, all these methods we have learned from this article will benefit you in your real-life problems. Moreover, the workbook is there for you to download and practice yourself.  Lastly, if you have any questions, comments, or any kind of feedback, please let me know in the comment box. And please visit our website ExcelDemy to explore more.

## Related Articles

#### Masum Mahdy

Hi there! I am Mahdy, a graduate of Naval Architecture from BUET, currently working as an Excel & VBA Content Developer in ExcelDemy. You are gonna find my published articles on MS Excel and other topics of my interest here in ExcelDemy's blog. You are most welcome to my profile!

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF