Hosne Ara
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).
- 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.
- Next, double click on the cell where you want to locate the merged data (here, Cell D5).
- 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.
Read More: How to Merge Cells in Excel Table (7 Ways)
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.
- After you are done with the customization, again copy the data from the notepad.
- 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.
Read More: How to Merge Multiple Cells in Excel at Once (3 Quick Ways)
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.
Steps:
- Firstly, type the below formula in Cell E5 and press Enter from the keyboard.
=B5&" "&C5&" "&D5
- 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.
- Finally, we will get the below output and the good part is we did not lose any data during the merging process.
Read More: How to Merge Cells in Excel Vertically Without Losing Data
Similar Readings
- Merge Data in Excel from Multiple Worksheets (3 Methods)
- VBA to Merge Cells in Excel (9 Methods)
- How to Unmerge Cells in Excel (7 Easy Methods)
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)
- 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.
Read More: How to Merge Two Cells in Excel without Losing Any Data
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)
- 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.
Read More: How to Merge Text from Two Cells in Excel (7 Methods)
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)
- 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.
Read More: How to Merge Text from Two or More Cells into One Cell (Easiest 6 ways)
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.
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.
Read More: How to Merge and Center Cells in Excel (3 Easy Methods)
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.
