Merging cells only keeps the upper-left and discards other values issue in Excel.
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 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 in the left top 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.
Clipboard Launcher in Excel
- 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.
Values are copied into the clipboard launcher area.
- 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.
Pasting the cell values into the cells without losing it’s contents
- All values will now be added into the cell as one continuous block of text. However, the cell’s formatting and the formatting of its contents will be completely removed.
Before
After
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 its 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.
Basic Excel Formula to Merge Column without Losing its values.
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")
Excel Formula to Merge Columns separated by comma and spaces between the values.
Method 3: Merge Multiple Columns Contents 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 | It’s Purposes |
|---|---|
| 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
Merged Columns using Excel Functions
That’s it.
Feel free to comment us below, if you have any queries about the above topic and find more interesting excel tutorials on our homepage: Excel24x7.com.
Top comments (0)