How to Merge Cells Using Excel Formula (8 Simple Ways)

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, I will show you the formula to merge cells in Excel.


Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.


8 Suitable Formulas 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.

Formula to Merge Cells in Excel


1. Merge 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 appears. We will click on the Merge and Center.

Merge Multiple Cells Using the Merge & Center Feature in Excel

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

three cells have been merged into one

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 Across option will merge the selected cells in the same row

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

Merge Cells will not center the content of the cells


2. Merge 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 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.

Merge Multiple Cells with Contents Using Merge and Center

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.

Formula to Merge Cells in Excel

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

The merged cell contains only the text of cell B2 (“Merge”) before merging


3. Use 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 & " " & C5
Formula Breakdown:

The two ampersand symbols (&) will join the text in cell B5, space (“ ”) and text in cell C5.

Use Ampersand Symbol (&) to Merge Multiple Cells in Excel

  • Upon pressing ENTER, we will see that cell E5 has now the Full Name of the first employee.

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.

drag the fill handle 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.

ach cell in the Full Name column has the full name of the respective employee

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"
Formula Breakdown:

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

Ampersand (&) in Excel

  • Upon pressing ENTER, we will see that cell E5 has now the following text in it: Walter White is 30 Years Old.

E5 has now the following text: 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.

drag the fill handle 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.

each cell in the About the Person column has a similar text


4. Apply the CONCATENATE Formula to Merge Cells in Excel

In addition to the ampersand symbol (&), we can also the CONCATENATE formula to merge cells in Excel.

Step 1:

  • First, enter the below formula in cell E5.
=CONCATENATE(D5, " years old ", B5, " ", C5)
Formula Breakdown:

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.

Apply the CONCATENATE Formula to Merge Cells in Excel

  • Upon pressing ENTER, we will see that cell E5 has now the following text in it: 30 Years Old Walter White.

cell E5 has now the following text : 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.

drag the fill handle 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.

Formula to Merge Cells in Excel


Similar Readings


5. Use 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.

Use the Justify Feature to Merge Cells in the Same Column

  • 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).

texts in all the cells under the Information column have been merged into the first or top-most cell

  • Now, we will click on Merge and Center in the Alignment section of the Home.

Formula to Merge Cells in Excel

  • Finally, the merged text in the Information column will be centered in cell B5.

the merged text in the Information column will be centered in cell B5


6. Insert 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.

Insert the TEXT Formula in Excel to Correctly Display Numbers in Merged Cells

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")
Formula Breakdown:

The TEXT function 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.

TEXT Formula in Excel

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

date values are now shown in the correct format


7. Find Merged Cells Quickly Using Find and Replace Tool

We can use the Find and Replace tool in Excel to quickly find out 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 >>

Formula to Merge Cells in Excel

Step 2:

  • Some options will appear. We will click on the Format drop-down.

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.

Formula to Merge Cells in Excel

Step 3:

  • Now, we will click on the Find All button from the Find and Replace.

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.

all of the merged cells in the worksheet along with the cell addresses


8. Unmerge 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 appears. We will click on the Unmerge Cells.

Unmerge the Combined Cells in Excel

  • Now, All the merged cells in the Full Name column will be unmerged.

● All the merged cells in the Full Name column will now be unmerged


Things to Remember

  • You can use the below keyboard shortcuts to merge cells.
    • To activate 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 texts 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.

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 do leave a comment below. Have a great day!!!


Related Articles

ASM Arman

ASM Arman

Hi there! I am ASM Arman. I Completed B.Sc. in Naval Architecture and Marine Engineering. I take a great interest in learning about new technologies and sharing my ideas and thoughts on them with others. Please do reach out to me if you have any queries or recommendations. Have a great day!!!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo