Merge is an excellent and powerful tool in Excel that lets you merge or combine multiple across different columns or cells under the same column. The merge feature in Excel has many usages. For example, we can merge the first and last names of people to get their full names. In this tutorial, we’ll show you the formula to merge cells in Excel.
Let’s assume a scenario where we have an Excel worksheet that contains information about the employees of a company. The worksheet has the First Name, Last Name, Birth Day, Age of each employee in the company. We will use the formula to merge the cells in the Excel worksheet in different ways. The image below shows the worksheet we are going to work with.
1. Merging Multiple Cells Using the Merge & Center Feature in Excel
We can use the Merge and Center feature in Excel to merge multiple cells in the same row. Just do the following.
Step 1:
- We have a text “Merge and Center in Excel” in cell B2. We will merge it with the adjacent C2 and D2 cells in the same row. So. the three cells will be merged into one and the text will cover the entire area of these 3 cells (B2, C2, D2).
- First, we will select the 3 cells we want to merge (B2, C2, D2). Then, we will go to the Merge and Center drop-down menu in the Alignment section under the Home.
- Upon clicking the Merge and Center drop-down menu, we will see a list of different types of merge options appear. We will click on the Merge and Center.
- Now, we will see that the three cells have been merged into one. The address of the merged cell is B2. The text now covers the space of all 3 cells.
Step 2:
- We can also try the other merge options from the Merge and Center drop-down menu. The Merge Across option will merge the selected cells in the same row into one large cell.
- The merge Cells option will merge the selected cells into one cell but it will not center the content of the cells in the new merged cell.
2. Combining Multiple Cells with Contents Using Merge and Center
Step 1:
- In the example above, we have merged the content of a single cell into 3 cells. But if we try to merge multiple cells at once with different contents, then the merge feature will combine the cells differently. In the example below, we have 3 pieces of text in the 3 cells (B2, C2, D2).
- We will click on the Merge and Center from the Merge and Center drop-down menu.
Step 2:
- A warning dialogue box will appear that will tell you that merging cells will only keep the content of the upper-left value while discarding the contents of the rest of the cells. In this example, merging cells will only keep the content or text of cell B2 (“Merge”) while stripping away the contents of the rest of the cells (C2, D2).
- We will click on OK.
- Now, we will see that the 3 cells have been merged into one large cell with the cell address B2. But it contains only the text of cell B2 (“Merge”) before merging.
Read More: How to Merge Cells in Excel Without Merging Actually
3. Using Ampersand Symbol (&) to Merge Multiple Cells in Excel
We can also use the Ampersand symbol (&) to merge or join text or contents of multiple cells. For example, we will join the First Name in cell B5 and the Last Name in cell C5 using the ampersand symbol (&) to generate the Full Name
Step 1:
- First, we will write down the following formula cell E5.
=B5 & " " & C5The two ampersand symbols (&) will join the text in cell B5, space (“ ”), and text in cell C5.
- Upon pressing ENTER, we will see that cell E5 has now the Full Name of the first employee.
Step 2:
- We will now drag the fill handle of cell E5 to apply the formula to the rest of the cells.
- Each cell in the Full Name column has the full name of the respective employee in that row.
Step 3:
- We can also add additional text between the cells before joining them using the ampersand (&) symbol.
- Write the following formula in cell E5.
=B5 & " " & C5 & " is " & D5 & " years old"The ampersand symbols (&) will join the text in cell B5, space (“ ”), text in cell C5, text in cell D5, and two additional strings: “is” and “years old”.
- Upon pressing ENTER, we will see that cell E5 has now the following text in it: Walter White is 30 Years Old.
Step 2:
- We will now drag the fill handle of cell E5 to apply the formula to the rest of the cells.
- Finally, we will see that each cell in the About the Person column has a similar text.
Read More: How to Merge Multiple Cells without Losing Data in Excel
4. Applying the CONCATENATE Formula to Merge Cells in Excel
In addition to the ampersand symbol (&), we can also the CONCATENATE function to merge cells in Excel.
Step 1:
- First, enter the below formula in cell E5.
=CONCATENATE(D5, " years old ", B5, " ", C5)The CONCATENATE formula is self-explanatory. It takes 5 arguments.
- The first one is the Age (D5).
- The second argument is a piece of text “ years old ”.
- The third argument is the First Name (B5) of the employee.
- The fourth argument is a space (“ ”).
- And the last one is the Last Name (C5) of the employee.
- Upon pressing ENTER, we will see that cell E5 has now the following text in it: 30 Years Old Walter White.
Step 2:
- We will now drag the fill handle of cell E5 to apply the formula to the rest of the cells.
- Finally, we will see that each cell in the About the Person column has a similar text.
Similar Readings
- VBA to Merge Cells in Excel
- How to Merge Cells Vertically Without Losing Data in Excel
- How to Merge Data from Multiple Workbooks in Excel
5. Using the Justify Feature to Merge Cells in the Same Column
Till now, we have learned how to merge or combine cells in the same row. But we can also merge or join cells in the same column using the Justify feature in Excel.
Step 1:
- First, we will select all the cells in the same column that we want to merge or combine.
- Then, we will go to the Fill drop-down menu in the Editing section of the Home.
- A new menu with different types of Fill options will appear. We will select Justify.
- We will now see that texts in all the cells under the Information column have been merged into the first or top-most cell (B5).
- Now, we will click on Merge and Center in the Alignment section of the Home.
- Finally, the merged text in the Information column will be centered in cell B5.
6. Inserting the TEXT Formula in Excel to Correctly Display Numbers in Merged Cells
While using the ampersand (&) or CONCATENATE functions to merge cells in Excel, we will face a problem working the dates. Like the image below, the date values will be lost in the format due to merging the cell values.
We can avoid this problem using the TEXT function in Excel. Follow the below steps.
Steps:
- First, enter the below formula in cell E5.
="Birthday of " & B5 & " " & C5 & " is " & TEXT(D5, "dd/mm/yyyy")The TEXT function in Excel takes a value (D5) as the first argument and a text format (“dd/mm/yyyy”) as the second argument. It will return the text or the first augment in the text format that we have given it as the second argument.
- If we apply the formula to the rest of the cells in the About The Person column, we will see that the date values are now shown in the correct format.
Read More: How to Merge Text from Two Cells in Excel
7. Finding Merged Cells Quickly Using Find and Replace Tool
We can use the Find and Replace tool in Excel to quickly find all the merged cells in a worksheet.
Step 1:
- First, we will press CTRL+F to activate the Find and Replace tool in Excel. A window titled Find and Replace will appear.
- We will click on the Options >>.
Step 2:
- Some options will appear. We will click on the Format drop-down.
- A new window will appear. We will click on the Alignment.
- Then, we will check the box beside Merged cells.
- Finally, click on OK.
Step 3:
- Now, we will click on the Find All button from the Find and Replace.
- We can now see all of the merged cells in the worksheet along with the cell addresses.
Read More: How to Merge Cells in Excel with Data
8. Unmerging the Combined Cells in Excel
We can use the Unmerge Cells feature from the Merge and Center drop-down menu to unmerge the merged or combined cells in a worksheet.
Steps:
- First, we will select the merged cells. Then, we will go to the Merge and Center drop-down menu in the Alignment section under the Home.
- Upon clicking the Merge and Center drop-down menu, we will see a list of different types of merge options appear. We will click on the Unmerge Cells.
- Now, All the merged cells in the Full Name column will be unmerged.
Things to Remember
- You can use the below keyboard shortcuts to merge cells.
- To activate the Merge Cells option: ALT H+M+M
- To Merge & Center: ALT H+M+C
- Shortcut for Merge Across: ALT H+M+A
- To Unmerge Cells: ALT H+M+U
- When merging multiple cells with text values, you can make a copy of the original data. Making a copy of the original data will prevent the risk of losing the data due to merging.
Download Practice Workbook
Download this practice book to exercise the task while you are reading this article.
Conclusion
In this article, we have learned the formula to merge cells in Excel in different ways. I hope from now on you can use the formula to merge cells in Excel very easily. However, if you have any queries or recommendations about this article, please leave a comment below. Have a great day!!!
