When merging cells containing data into 1 cell, Excel retains the data in the first cell only, data in the remaining cells are deleted. So to retain all data in the cells you need to merge, you can not use the Merge feature in Excel but need to use it in a different way. The following article details instructions How to combine 2 or more cells in Excel without losing data.
For example, with the collected population list, it is necessary to combine the field of first and last names into 1 cell and retain all the data. Especially when every school full name and first name of an individual automatically enter the line.
For example, here, Nguyen Van An and Le Thi My share the same weapon to make 2 cells to make one cell, especially Le Thi My Chi automatically goes down the line:
To combine two or more cells in Excel to ensure no data loss, follow these steps:
Step 1: Open the Excel file containing the data to merge -> press Alt + F11 to open the VBA editor to create Macros -> on the Insert tab -> Module:
Step 2: Enter the following code content, with the module name 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 importing, click File -> Close and Return to Microsoft Excel to return to MS Excel:
Step 4: So you have created a macro with 2 or more cells without losing data, the rest you create a shortcut combination to use this Macros. Click the Developer tab -> Macros:
In case the Developer does not appear on the Ribbon, you do the following: Go to the File tab -> Options -> the dialog box appears option Custom Ribbon -> in the Main Tabs area select the Developer -> click OK:
Step 5: A dialog box appears, the name of the newly created macros is displayed -> click select the name of macros -> select Options to set the shortcut for macros:
Step 6: Default shortcut key combination Ctrl and 1 any key of your choice, for example, here set Ctrl + e key combination to use macros to combine 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 keys instead of the newly created Macros. For example, select cells C1 and C2 -> press Ctrl + e -> 2 cells to be combined into 1 cell without losing data. Special data line breaks:
Similarly, performing the merging of cells for the remaining exports results:
The above is a detailed guide on how to combine 2 or more cells in Excel without losing data. Good luck!