Masum Mahdy
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.
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.
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:
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:
"".
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:
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:
- 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:
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.
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.
