You can merge two Cells in Excel using CONCATENATE function or the “&” (ampersand) operator. Though Excel has a merge tool, when you merge cells in Excel with data, you will lose the data on the right-hand cell.
In the sheet below, when I tried to merge Cells B5 and C5, I received the warning shown.
And here is the result. I lost the second name!
This guide will show you how to merge cells in excel without losing your data.
How to Merge Cells in Excel with CONCATENATE
The table below shows student names in two columns. First Name and Last Name. Say your boss sent you a sheet with thousands of names. You are required to to merge the names into a single column.
In this example, I will demonstrate how to merge the two cells into one using the CONCATENATE.
The syntax of this Excel function is:
CONCATENATE(text1, [text2], [text3])
For this example, text1 and text2 are the two cells you wish to merge.
To merge cells in columns B and C into a single column in D, follow the steps below:
- On cell D5, enter the formula:
- Then select cell B5 and enter a coma (,). Next, select cell C5. Next to C5 enter a closing bracket “)” and press Enter. The two cells will merge into a single cell as shown in the second image below. But there is a problem. There is no space between the names!
- To add a space between First and Last name, modify the formula as shown below:
=CONCATENATE(B5, " ", C5)
- Here is the result using the modified formula above.
=CONCATENATE(C5, ",", " ", B5)
The formula simply reversed the position of the cells. It also includes a comma (,) and a space. Here is the result in Excel.
How to Merge Cells in Excel with “&” (Ampersand)
Another easy way to merge cells in Excel is to use the “&” (Ampersand) operator.
The syntax of & operator is:
text1 & [text2] & [text3]
It is very similar to CONCATENATE. But instead of separating the cells with a comma (,), the cells are separated with “&”.
Refer to the image above. In this example, I will show how to merge cells C5 and D5 in E5.
To merge cells in Excel using “&” (Ampersand) follow the steps below:
- On cell E5, enter the formula:
- The result will merge the cells without any space.
- To merge the cells in Excel and include a space between the names, modify the formula as shown below:
To merge cells in Excel is as simple as I have shown in this guide. The two methods shared in this guide can also be used to merge more than two cells in Excel. I hope it was helpful. If you want to learn more about the methods used in this guide click How to Concatenate Columns and Strings.
If you have any question regarding how to merge cells in excel, use the “Leave a Reply” form at the end of this page. Better still, you could share your experience.