The issue “Merging cells only keeps the upper-left value and discards others” happens when you try to merge cells that contain more than one value. In this tutorial, I’ve explained all the methods to combine data from multiple columns without losing any information from your sheet.
Method 1: Using the Clipboard to Merge Columns into a One Cell
The Clipboard method allows users to combine multiple columns into one cell easily. To use this method, you’ll need to open the Clipboard launcher in Excel.
- Under the HOME tab on the top left of the sheet.
- You may notice the word “Clipboard” on the screen. Next to it, there is a small arrow icon, which is the Clipboard Launcher. Click on it to open the clipboard panel.
- Once the Clipboard launcher is open, select the columns you want to copy and press CTRL + C on your keyboard.
- Now, the values from the selected columns are copied into the Clipboard launcher area, as shown in the image below.
- Now, select the target cell. In this case, let’s choose cell F10. Double-click on the cell to activate it, then press CTRL + V on your keyboard to paste the content into the cell.
- All values will now be added to the cell as one continuous block of text. However, the cell’s formatting and the formatting of its contents will be completely removed.
Method 2: Merge Using Excel Formulas:
If you want to merge columns in Excel without losing any content, you can use basic Excel features like the ampersand (&) operator and other built-in Excel functions to do this easily.
Basic Merging Formula:
The basic formula to merge multiple columns without losing their data is,
=B4 & " " & C4 & " " & TEXT(D4, "dd-mm-yyyy")
By using the above formula, the contents from different columns will be combined with a space between them. The TEXT function is used to format the date properly so that it appears in the correct Excel date format.
Merging With Space and Comma Between the Values:
In the above example, the formula adds only a space between the contents. But in the example below, the formula adds a comma and a space while combining values from multiple columns. You can replace the comma with any other character or text based on your requirement.
=B4 & ", " & C4 & ", " & TEXT(D4, "dd-mm-yyyy")
Method 3: Merge Multiple Columns of Content Using Excel Functions
You can merge columns and their values in Excel using functions like TEXTJOIN or CONCAT. If you are using an older version of Excel, you can use the CONCATENATE function to combine the contents instead.
|
Function |
Its Purpose |
|---|---|
|
TEXTJOIN |
Combines text from multiple cells using a delimiter, optionally ignoring blanks. |
|
CONCAT |
Joins text from multiple cells or ranges into one string (modern version). |
|
CONCATENATE |
Joins text from multiple cells into one string (older version of CONCAT). |
The formulas are:
=TEXTJOIN(", ", TRUE, B4, C4, TEXT(D4, "dd-mm-yyyy"))
=CONCAT(B4, ", ", C4, ", ", TEXT(D4, "dd-mm-yyyy"))
=CONCATENATE(B4, ", ", C4, ", ", TEXT(D4, "dd-mm-yyyy")) //For older versions
That’s it. This tutorial was originally published on How to Merge & Combine Columns without Losing Data in Excel?