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

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.

Sample Dataset: How 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!


Download Practice Workbook

Download the following workbook and practice along with that.


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:
1. Type the Full Name Edgar Allan Poe manually. Select the range of cells E5:E10.

Keyboard Shortcut Ctrl+E for the Flash Fill

2. 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! 👇

Change in Pattern with Flash Fill

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

4 Easiest Methods to Combine Cells in Excel

1. Apply Excel Function to Combine Cells

1.1 Use the TEXTJOIN Function

Let’s see how it works.

Steps:
1. Enter the following formula in Cell E5.

=TEXTJOIN(" ", TRUE, B5:D5)

Combine Cells with TEXTJOIN

2. Drag the fill handle icon.

Drag down the Fill Handle

See the following screenshot for the result.

Output: Combine Cells with TEXTJOIN

🔗 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

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:
1. 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.

Combine Cells with CONCAT

2. Pull the fill handle icon.

Drag down the Fill Handle

See the result below. 👇

Output: Combine Cells with CONCAT

🔗 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

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

Steps:
1. Write the following formula in Cell E5 and press Enter.

=CONCATENATE(B5, " ", C5, " ", D5)

Combine Cells with CONCATENATE

2. Drag or double-click on the fill handle icon.

Drag down the Fill Handle

So, the following screenshot shows the result that we’ve got after applying the CONCATENATE function.

Output: Combine Cells with CONCATENATE

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


2. Use the Flash Fill Feature

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:
1. Manually type the Full Name Edgar Allan Poe in Cell E5. MS Excel will sense the pattern you provide here.

Combine Cells with the Flash Fill

2. Start to type the Full Name in Cell E6, type Er. (Because Edgar also starts with E.)

You will see that MS Excel automatically shows the rest of the name in Cell E6, not only that, Excel also shows a preview of the future outputs in Cells E7:E10.

3. Hit Enter.

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.

Turn on Auto 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.

Read More: How to Combine Two Cells in Excel (6 Quick Methods)


3. Join Cells with the Ampersand (&) Symbol

To apply this method, execute the following steps.

Steps:
1. Type the following formula in Cell E5.

=B5&" "&C5&" "&D5

Combine Cells with the Ampersand

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

Drag down the Fill Handle

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

Output: Combine Cells with the Ampersand

🔗 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 with Excel

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

Steps:
1. Copy the range of cells E5:E10.

Combine Cells with Notepad

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

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

5. Replace All > Cancel.
6. Copy the full text again >  Click on Cell E5 > Ctrl+V.

Output: Combine Cells with NotepadJob done!

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


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.

Remove Unwanted Characters in Excel

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:
1. Type the following formula in Cell E5:

=TRIM(SUBSTITUTE(TEXTJOIN(" ", TRUE, B5:D5), "-", ""))

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

Now, look at the result in the following screenshot!

Output: Remove Unwanted Characters in Excel

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


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:
1. Choose the cells you want to merge, B2:E2.

2. Go to the Home tab > the Alignment group > Merge & Center.

Or,
i. Select the cells you want to merge, B2:E2.
ii. Ctrl +1 > the Alignment tab > Select the following options.

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

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

Leave a reply

ExcelDemy
Logo