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. “Merging cells only keeps the upper-left value and discards others” all the methods multiple columns any information 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. Clipboard method combine multiple columns into one cell Clipboard launcher 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. Under the HOME tab on the top left of the sheet. HOME 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” arrow icon Clipboard Launcher 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. Once the Clipboard launcher is open, select the columns you want to copy and press CTRL + C on your keyboard. Clipboard launcher columns Now, the values from the selected columns are copied into the Clipboard launcher area, as shown in the image below. 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. 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. cell F10 paste the content 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. 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. continuous block of text cell’s formatting formatting of its contents 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. without losing any content ampersand (&) Basic Merging Formula: The basic formula to merge multiple columns without losing their data is, =B4 & " " & C4 & " " & TEXT(D4, "dd-mm-yyyy") =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. combined with a space TEXT format the date 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. space comma and a space replace the comma =B4 & ", " & C4 & ", " & TEXT(D4, "dd-mm-yyyy") =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. TEXTJOIN CONCAT older version of Excel CONCATENATE 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). 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). Function

Its Purpose Function Function Its Purpose Its Purpose TEXTJOIN

Combines text from multiple cells using a delimiter, optionally ignoring blanks. TEXTJOIN TEXTJOIN Combines text from multiple cells using a delimiter, optionally ignoring blanks. Combines text from multiple cells using a delimiter, optionally ignoring blanks. CONCAT

Joins text from multiple cells or ranges into one string (modern version). CONCAT CONCAT Joins text from multiple cells or ranges into one string (modern version). 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). CONCATENATE CONCATENATE Joins text from multiple cells into one string (older version of CONCAT). 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 =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? How to Merge & Combine Columns without Losing Data in Excel

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Avoid Data Loss: Merge Excel Cells with TEXTJOIN & CONCAT Functions!

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps