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.
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.
1. Type the Full Name Edgar Allan Poe manually. Select the range of cells E5:E10.
2. Press Ctrl+E.
There you are! The result is ready.
- 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 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.
1. Enter the following formula in Cell E5.
=TEXTJOIN(" ", TRUE, B5:D5)
2. Drag the fill handle icon.
See the following screenshot for the result.
- 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.
1. Type the following formula in Cell D5.
Or, type CONCAT( and select the range of cells you want to join, and close the parenthesis.
2. Pull the fill handle icon.
See the result below. 👇
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.
1. Write the following formula in Cell E5 and press Enter.
=CONCATENATE(B5, " ", C5, " ", D5)
2. 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.
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.
1. Manually type the Full Name Edgar Allan Poe in Cell E5. MS Excel will sense the pattern you provide here.
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.
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.
1. Type the following formula in Cell E5.
=B5&" "&C5&" "&D5
2. 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.
- 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.
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.
1. Copy the range of cells E5:E10.
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.
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.
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!
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.
1. Choose the cells you want to merge, B2:E2.
2. Go to the Home tab > the Alignment group > Merge & Center.
i. Select the cells you want to merge, B2:E2.
ii. Ctrl +1 > the Alignment tab > Select the following options.
3. Press OK.
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.