How to Merge Cells in Excel without Losing Data

Fill Justify to Merge Cells without Losing Data (Getting Values in One Cell from Multiple Cells)

If you want to merge values from multiple cells, you only need to do it once, and these values are in a vertical order, then using the fill justify is the best way.

In the above example, we have values in the range A1:A5, and now you need to merge these values in cell A1 only.

1. First, you need to make sure that your column is wide enough that, when you combine all five values, there is enough space in cell A1.
   
2. From here, you need to go to the Home Tab.
   
3. Now, on the Home tab, go to the Editing group and click on the Fill drop-down.
   
4. Next, from the Fill drop-down, click on the “Justify” option.
   

The moment you click “Justify”, it will combine the values from all the cells into a single cell, i.e. A1.

As I said earlier, you need to make sure that the width of the column needs to be enough so that it can merge all the values in a single cell. Otherwise, the rest of the values will get split into the second cell.

Keep in mind that Fill Justify only works with text in a single column. It doesn’t combine numbers or cells with formulas, and it doesn’t merge the cells themselves: it just moves all the text into the first cell.

Formulas to Merge Cells without Losing Data (CONCATENATE and TEXTJOIN)

If you want to merge cells on a real-time basis and don’t want to lose data, the best way is to use a formula. And in Excel, we have two different functions to create these formulas.

Out of these two, my personal favorite is TEXTJOIN. It’s a newer function (Excel 2019 and later, and Microsoft 365) and easier to use than CONCATENATE.

=TEXTJOIN(" ",TRUE,A1:A5)

TEXTJOIN has three arguments that you need to define:

• delimiter: You can use a delimiter that you want to add in between values that you want to merge into one cell.
• ignore_empty: A TRUE or FALSE to define if you want to ignore empty cells or not.
• range_array: A range/array from where you want to merge values.

And in the same way, you can use CONCATENATE to create a formula to merge values into a single cell.

=CONCATENATE(A1,” “,A2,” “,A3,” “,A4,” “,A5)

Unlike TEXTJOIN with TRUE, CONCATENATE doesn’t skip empty cells, so a blank cell in the range leaves a double space in the result. You can get the same result with the & operator, which works in every version of Excel:

=A1&" "&A2&" "&A3&" "&A4&" "&A5

And if you want each value on its own line in the merged cell, use a line break as the delimiter and turn on Wrap Text for the cell:

=TEXTJOIN(CHAR(10),TRUE,A1:A5)

Merge the Cells and Keep the Combined Value

A formula gives you the combined value in another cell. To have it in the merged cell itself, follow these steps:

1. First of all, create the TEXTJOIN formula in an empty cell.
2. After that, copy that cell and paste it as values (Ctrl + Alt + V, then V) into the upper-left cell of the range you want to merge.
3. Next, clear the other cells of the range.
4. In the end, select the range and click Home ➜ Merge & Center.

VBA Code to Merge Values into a Cell without Losing Data

You can use a VBA code to merge multiple cells and not lose data. Below we have a code that you can use.

Sub vba_merge_with_values()
 
Dim val As String
Dim rng As Range
Dim Cell As Range
 
Set rng = Selection
 
For Each Cell In rng
    val = val & " " & Cell.Value
Next Cell
 
Application.DisplayAlerts = False 'skip Excel's warning about losing values
With rng
    .Merge
    .Value = Trim(val)
    .WrapText = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
Application.DisplayAlerts = True
 
End Sub

To use this code, you need to select the range that you want to merge and then run this code.

This code takes the values from all the cells one by one, then merges all those cells, and then adds that combined value to the merged cell, as you can see in the above example (There’s one thing that you need to know: when you run a code, you can’t undo it).

The DisplayAlerts lines turn off the warning that Excel shows when you merge cells with more than one value. Without them, the code stops at that warning until you click OK. The code also declares the Cell variable, so it works even with Option Explicit at the top of the module.

Center Across Selection Instead of Merging

If you only want a heading to look centered over several columns, you don’t need to merge at all. Select the range, press Ctrl + 1, and on the Alignment tab, choose Center Across Selection in the Horizontal list. The text looks merged, but each cell stays separate, so sorting, copying and selecting work as normal.

Frequently Asked Questions