How to Merge Cells in Excel without Losing Data

While working with data in Excel, sometimes you need to merge multiple cells into a single cell. But here the problem is when you do that you can only get the value from the upper left cell from all the cells you want to merge into one.

Now, to solve this problem there are a few methods that you can use to merge cells without losing data, which means getting data from multiple cells into one.

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

If you want to merge values from multiple cells, you want to do it for 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 needs to be wide enough that when you combine all the five values there should be enough space in cell A1.
   
2. From here, you need to go to the Home Tab.
   
3. Now from the home tab, you need to go to the editing group and then click on the fill dropdown.
   
4. Next, from the fill drop down you need to click on the “Justify” option.
   

The moment you click the “Justify”, it will merge 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 to the second cell.

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

If you want to merge cells on a real-time basis and don’t want to lose data too. 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, this is a new function and easy to use as compared with 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 the CONCATENATE to create a formula to merge values into a single cell.

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

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
 
Set rng = Selection
 
For Each Cell In rng
    val = val & " " & Cell.Value
Next Cell
 
With rng
    .Merge
    .Value = Trim(val)
    .WrapText = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
End With
 
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 values from all the cells one by one, then merges all those cells, and then add that combined value in the merged cell as you can see in the above example (There’s one thing that you need to know when running a code, you can’t use undo option).