Merge Cells in Excel Without Losing Data

When merging cells containing data into one cell, Excel only retains data in the first cell. To preserve all data, use an alternative method.

For instance, when combining first and last name fields of an individual into one cell, especially when each name automatically goes to the next line.

For example, Nguyen Van An and Le Thi My share the same household. To combine two cells into one, especially when Le Thi My Chi automatically goes to the next line:

To merge two or more cells in Excel without losing data, follow these steps:

Step 1: Open the Excel file containing the data to be merged -> press Alt + F11 to open the VBA editor to create Macros -> go to the Insert tab -> Module:

Step 2: Enter the following code into a module named MrgCll:

Sub MrgCll()
Dim Cll As Range, Temp As String
Application.DisplayAlerts = False
On Error Resume Next
If Selection.MergeCells = False Then
For Each Cll In Selection
If Cll <> '' Then Temp = Temp + Cll.Text + vbCrLf
Next Cll
Selection.Merge
Selection.Value = Left(Temp, Len(Temp) - 1)
Else
Selection.UnMerge
End If
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
End Sub

Step 3: After entering the code, select File -> Close and Return to Microsoft Excel to return to MS Excel:

Step 4: You have now created a Macro to merge 2 or more cells without losing data, the next step is to create a shortcut key to use this Macro. Select the Developer tab -> Macros:

If the Developer tab does not appear on the Ribbon, follow these steps: Go to the File tab -> Options -> a dialog box appears, choose Custom Ribbon -> in the Main Tabs section, check the box next to Developer -> click OK:

Step 5: A dialog box appears, the name of the newly created macros is displayed -> select the macro name -> choose Options to set a shortcut key for the macro:

Step 6: Set the default shortcut key combination to Ctrl and any other key of your choice, for example, here set the shortcut key combination Ctrl + e to use the macro to merge two or more cells without losing data:

Step 7: Click the Close icon to close the Macros: dialog box:

Step 8: Finally, select the data range to merge cells and use the shortcut key instead of the newly created macro. For example, select cells C1 and C2 -> press the shortcut key combination Ctrl + e -> the two cells are merged into one without losing data. Especially, the data will automatically wrap:

Perform the same merging operation for the remaining cells to get the desired result:

Here is a detailed guide on How to merge 2 or more cells in Excel without losing data. Wishing you all the best!