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

In this article, I will discuss how you can merge multiple excel cells without losing data. While working in Microsoft Excel, you may want to join several for better representation of data. In this article, I will combine multiple cells using excel functions, notepad, and clipboard.


Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.


6 Methods to Merge Multiple Cells without Losing Data in Excel

1. Apply Excel Clipboard to Join Several Cells without Losing Data

In this method, I will show you the application Excel Clipboard to join multiple cells. Suppose I have a dataset containing several people’s first names and last names. Now I will combine these names following the below steps.

Steps:

  • First, 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

  • As a result, the below clipboard appears. Now 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

  • Next, 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

  • Then, click on the copied data on the clipboard. Consequently, data from all the cells are placed in Cell D5, obviously merged and we did not lose any data.


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

We can join multiple cells without losing data using a notepad. Similar to the previous method, I will merge several cells containing first and last names.

Steps:

  • First, copy (using Ctrl + C) all the data and paste (using Ctrl + V) it into a notepad. After you paste the data into the notepad, customize the data according to your requirement.

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

  • After you are done with the customization, again copy the data from the notepad.

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

  • Then double click in Cell D5 and paste the copied data there. Finally, you will see data from all the cells are merged and no original data is lost.


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

Now, I will use an excel formula to join multiple cells without losing data. While writing the formula, the ampersand (&) operator will be used to concatenate data from different cells. Let’s assume we have a dataset (B5:D10) containing several people’s names,  residing cities, and professions. Now, I will combine these cells following the below instructions.

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

Steps:

  • Firstly, type the below formula in Cell E5 and press Enter from the keyboard.
=B5&" "&C5&" "&D5

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

  • As a consequence, we will see data from Cell B5, C5, and D5 are merged in Cell E5. Now, we will 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

  • Finally, we will get the below output and the good part is we did not lose any data during the merging process.


4. Insert CONCAT Function to Join Multiple Cells in Excel

In this method, I will use the CONCAT function to join several cells without losing data. We will apply the function to the similar dataset that was used in the previous method.

Steps:

  • Type the below formula in Cell E5 and hit Enter.
=CONCAT(B5,", ",C5,", ",D5)

Insert CONCAT Function to Join Multiple Cells in Excel

  • Upon entering the formula, Excel will return the below result. We have used the Fill Handle tool to combine the rest of the cells.

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


5. Combine Various Cells to Avoid Data Losing with CONCATENATE Function

We can use the CONCATENATE function in excel to join multiple cells without losing data.

Steps:

  • Type the below formula in Cell E5. Then press Enter.
=CONCATENATE(B5,", ",C5,", ",D5)

Combine Various Cells to Avoid Data Losing with CONCATENATE Function

  • Consequently, you will get the below result.

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


6. Merge Multiple Cells Using TEXTJOIN Function without Losing Data in Excel

Now, we will use the TEXTJOIN function in excel to combine multiple cells without losing data.

Steps:

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

Merge Multiple Cells Using TEXTJOIN Function without Losing Data in Excel

  • Once you hit Enter, the above formula will return the combined text. We have used the Fill Handle tool to copy the formula to the rest of the cells. And, no data is lost during this joining process.

Here, 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 only. For instance, from the below dataset I want to combine the first name, last name, and profession.

Can You Merge Multiple Cells with Merge & Center Feature?

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

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


Conclusion

In the above article, I have tried to discuss several methods to merge multiple cells without losing data in excel elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo