How to Merge Cells in Excel in 2 Easy Ways

-

|

Introduction

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!

Advertisement

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.

How to Merge Cells in Excel with CONCATENATE

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:
=CONCATENATE(
  • 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!
How to Merge Cells in Excel with CONCATENATE
  • To add a space between First and Last name, modify the formula as shown below:
=CONCATENATE(B5, " ", C5)
The formula is the same as the first with a slight variation. The only difference is that the last one (above) includes quotes, ” ” with a space between them.
  • Here is the result using the modified formula above.
You can use this method to add other characters between the names. For instance if you want to reverse the position of the first and last name and include a comma after the last name, use the formula below.
=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)

How to Merge Cells in Excel with “&” (Ampersand)
Advertisement

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:
=B5&C5
  • 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:
=B5&" "&C5
Just like the previous example, adding a space is as simple as adding two quotes ” ” with a space between the quotes. The cells will merge with a space between them. See the image below.
How to Merge Cells in Excel with “&” (Ampersand)

Conclusion

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.

Other Helpful Guides

Additional Resources and References

LEAVE A REPLY

Please enter your comment!
Please enter your name here

FEATURED POSTS

network discovery keeps turning off server 2016

How to Fix Network Discovery If it Keeps Turning Off in Server 2016

Does network discovery keep turning off in your Windows server 2016? It is likely that one of its dependent services is not...
how to install windows 10 1903 update manually

How to Install Windows 10 1903 Update Manually

Windows 10 1903 Update was released in May, 2019. But some users are not yet offered the update via automatic update. The...
spotify web player not working

Spotify Web Player Not Working [Fixed]

Introduction Spotify Web Player may stop working for you with the following error messages: "Spotify Web Player an Error...
DISM.exe /Online /Cleanup-Image /Restorehealth

DISM.exe /Online /Cleanup-Image /Restorehealth Explained

What is DISM.EXE /Online /Cleanup-image /RestoreHealth? "DISM.exe /Online /Cleanup-Image /Restorehealth" is a DISM command that repairs issue with the...
DHCP Relay agent

DHCP Relay Agent: Configuration in Windows Server 2016

What is a DHCP Relay Agent? A DHCP Relay Agent allows DHCP clients in a different network subnet to...

TRENDING POSTS

Remote Desktop Connection

Remote Desktop Connection an Internal Error Has Occurred [Fixed]

Introduction I recently received the error message "Remote Desktop Connection an Internal Error Has Occurred". It was strange because...

Find My Samsung: Register and Use Samsung Find my Mobile

Introduction Ever wondered how you could find your Samsung phone if you lost it? Find my Samsung or Samsung...
What is the Difference Between PowerShell and CMD?

Windows Powershell vs CMD: Differences and Similarities

Introduction This short guide compares Windows PowerShell vs CMD (Windows command prompt). I will cover the history and nature...
Spotify No Longer Supports this Version of Microsoft Edge

Spotify No Longer Supports this Version of Microsoft Edge [Fixed]

Introduction When you open Spotify web player on Microsoft Edge, you may receive the error message "Spotify No Longer...
Windows 10 Won't Boot

Windows 10 Won’t Boot With Black Screen? 3 Ways to Fix It

Why Won't Windows 10 Boot Up? If your Windows 10 stops with a black screen, the first question in...

BEST OF ITECHGUIDES

Excel Substring: How to Get (Extract) Substring in Excel

Introduction An Excel substring is a portion of a text in a cell. For instance, if a cell contains...
spotify no longer supports this version of chrome

How to Fix “Spotify no Longer Supports this Version of Chrome”

Introduction Have you been frustrated by "Spotify no Longer Supports this Version of Chrome" error? You are not alone!...
iPhone SE vs iPhone 6s compared - which to buy

iPhone SE vs iPhone 6s Compared: Which to Buy

Introduction Considering whether to buy iPhone SE vs iPhone 6s? Not sure which is better? When I am trying...
WSUS Offline Update Generator

How to Use WSUS Offline Update Tool to Patch Offline Computers

What is WSUS Offline Update? WSUS Offline Update is a free tool that allows you to download Microsoft updates....
powershell add ad computers to array

How to Add AD Computers to a Powershell Array

Introduction There is one way to add AD Computers to a PowerShell array. A PowerShell array is a data...

RECENT POSTS

disable cortana windows 10 featured

How to Disable Cortana in Windows 10 (2 Methods)

Introduction Some Windows 10 users may not like Cortana. Solution? Disable Cortana. You are probably reading this because you...
bootrec /fixboot access is denied

How to Fix BootRec /FixBoot Access is Denied Error in Windows 10

Introduction BootRec /FixBoot Access is Denied Error in Windows 10? This error is likely caused by corrupt EFI directory.
Reset Windows 10

How to Reset Windows 10 (2 Methods)

Introduction If your Windows 10 is broken, one available way to fix it is to use Windows 10 reset....
system restore windows 10

How to Enable and Use System Restore in Windows 10

Introduction System restore in windows 10 is a very important tool that is probably ignored by most users. But...
windows has stopped this device code 43

How to Fix Windows Has Stopped this Device Code 43 Error

Introduction Sometimes you may receive "Windows has stopped this device code 43" error. This error is likely to come...
Windows could not automatically detect this network's proxy settings

How to Fix “Windows Could not Automatically Detect this Network’s Proxy Settings”

Introduction You suddenly lose the ability to connect to the internet. Then you run network troubleshooter and it returns...
printer offline

5 Ways to Restore Your Printer Online If Status is Offline

Introduction Is your printer offline? Setting it online is very simple but sometimes it is more than just setting...

How to Fix “BootMgr is Missing” Error in Windows 10

Introduction If you receive Fix "BootMgr is missing" Error in Windows 10, the default response is panic! But you...
windows 10 search featured

How to Fix Windows 10 Search If it Stops Working

Introduction Are you having troubles with Windows 10 search? Apparently, it is a fairly common problem with a number...
oooops, something went wrong. reload

How to Fix “Oooops something went wrong. reload” Spotify Error

Introduction If you receive "Oooops something went wrong. reload" Spotify error, do not panic. The fix is simpler than...

MUST READ

how to change wifi password

How to Change Wifi Password for Any Wifi Router

Introduction To change your Wifi password (PIN), you need the IP address and admin login for the router. Once...
dism /online vs /image

The Difference Between DISM /Online and DISM /Image Commands

What is DISM /Online vs /Image? In comparing DISM /online vs /image, while DISM /online targets the running operating...
Check Disk (CHKDSK) Windows 10

Check Disk (CHKDSK) in Windows 10: Syntax, Parameters, Examples

Introduction Check Disk (CHKDSK) is a Windows 10 command tool that checks a disk for errors, fixes errors, recovers...
How to get help in Windows 10

How to Get Help in Windows 10 in 5 Easy Ways

Introduction This "How to Get Help in Windows 10" guide discusses 5 easy ways you can find help while...
How to Make a Pivot Table in Google Sheets

How to Make a Pivot Table in Google Sheets

Introduction You can make a Pivot Table in Google Sheets to simplify analysis of complex data. A Pivot Table...

By using this website you agree to accept our Privacy Policy and Terms & Conditions