115 reads

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

by Excel24x7May 19th, 2025
Read on Terminal Reader
Read this story w/o Javascript
tldt arrow

Too Long; Didn't Read

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.

Company Mentioned

Mention Thumbnail
featured image - Avoid Data Loss: Merge Excel Cells with TEXTJOIN & CONCAT Functions!
Excel24x7 HackerNoon profile picture
0-item

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

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

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

Merged Columns using Excel Functions


That’s it. This tutorial was originally published on How to Merge & Combine Columns without Losing Data in Excel?


Trending Topics

blockchaincryptocurrencyhackernoon-top-storyprogrammingsoftware-developmenttechnologystartuphackernoon-booksBitcoinbooks