How to Merge Multiple Cells without Losing Data in Excel (6 Methods)

Method 1 – Applying Excel Clipboard to Join Several Cells without Losing Data

Steps:

  • Go to the Home tab and click on the Anchor button from the Clipboard group (at the bottom right corner).

Apply Excel Clipboard to Join Several Cells without Losing Data

  • The below clipboard appears.
  • Copy the cells (B5:C10) to merge using Ctrl + C.

Copying the cells will place the copied data in the clipboard, as shown in the below screenshot.

Apply Excel Clipboard to Join Several Cells without Losing Data

  • Double-click on the cell where you want to locate the merged data (here, Cell D5).

Apply Excel Clipboard to Join Several Cells without Losing Data

  • Click on the copied data on the clipboard. Data from all the cells were placed in Cell D5 and merged, and we did not lose any data.


Method 2 – Using Notepad to Combine Multiple Cells and Avoid Data Loss 

Steps:

  • Copy (using Ctrl + C) all the data and paste (using Ctrl + V) it into a notepad. Customize the data according to your requirements.

Use Notepad to Combine Multiple Cells and Avoid Data Losing in Excel

  • Copy the data from the notepad.

Use Notepad to Combine Multiple Cells and Avoid Data Losing in Excel

  • Double-click in Cell D5 and paste the copied data there. The data from all the cells are merged, and no original data is lost.


Method 3 – AMPERSAND (&) Symbol to Merge Several Cells without Losing Data

Steps:

  • Enter the below formula in Cell E5 and press Enter:
=B5&" "&C5&" "&D5

AMPERSAND ‘&’ Symbol to Merge Several Cells without Losing Data

  • The data from Cell B5, C5, and D5 are merged in Cell E5.
  • Use the Fill Handle (+) tool to copy the formula to the rest of the cells so that we can merge data from other cells too.

AMPERSAND ‘&’ Symbol to Merge Several Cells without Losing Data

  • You will get the below output and you did not lose any data.


Method 4 – Inserting the CONCAT Function to Join Multiple Cells 

Steps:

  • Enter the below formula in Cell E5 and press Enter:
=CONCAT(B5,", ",C5,", ",D5)

Insert CONCAT Function to Join Multiple Cells in Excel

  • Use the Fill Handle tool to combine the rest of the cells.
  • Excel will return the below result.

The CONCAT function concatenates the range B5:D5, separated by a delimiter and space.


Method 5 – Combining Various Cells to Avoid Data Loss with CONCATENATE Function

Steps:

  • Enter the below formula in Cell E5:
=CONCATENATE(B5,", ",C5,", ",D5)

Combine Various Cells to Avoid Data Losing with CONCATENATE Function

  •  Press Enter. You will get the below result.

The CONCATENATE function joins text strings from several cells (B5, C5 & D5)  and converts them into one text string as shown in the above screenshot.


Method 6 – Merging Multiple Cells Using the TEXTJOIN Function without Losing Data 

Steps:

  • Enter the below formula in Cell E5:
=TEXTJOIN(", ",TRUE,B5:D5)

Merge Multiple Cells Using TEXTJOIN Function without Losing Data in Excel

  • Press Enter, and the above formula will return the combined text.
  • Use the Fill Handle tool to copy the formula to the rest of the cells.

The TEXTJOIN function concatenates text strings from the range B5:D5 using a delimiter.


Can You Merge Multiple Cells with Merge & Center Feature?

You can try to merge several cells using the Merge & Center feature in Excel. Unfortunately, this method has a limitation. While combining multiple cells, this feature only keeps data from the leftmost cell. For instance, I want to combine the first name, last name, and profession from the dataset below.

Can You Merge Multiple Cells with Merge & Center Feature?

However, applying Merge & Center to selected cells returns the warning below that merging these cells will keep data from the leftmost cells only.

If you use the Merge & Center feature to combine multiple cells, you will lose data. To avoid this limitation, you can merge multiple cells using the above-mentioned 6 methods.


Download the Practice Workbook

You can download the workbook to practice.


<< Go Back To Excel Concatenate Multiple Cells | Concatenate Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo