How to combine 2 or more cells in Excel without losing data

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.

How to combine 2 or more cells in Excel without losing data Picture 1

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:

How to combine 2 or more cells in Excel without losing data Picture 2

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:

How to combine 2 or more cells in Excel without losing data Picture 3

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 

How to combine 2 or more cells in Excel without losing data Picture 4

Step 3: After importing, click File -> Close and Return to Microsoft Excel to return to MS Excel:

How to combine 2 or more cells in Excel without losing data Picture 5

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:

How to combine 2 or more cells in Excel without losing data Picture 6

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:

How to combine 2 or more cells in Excel without losing data Picture 7

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:

How to combine 2 or more cells in Excel without losing data Picture 8

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:

How to combine 2 or more cells in Excel without losing data Picture 9

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

How to combine 2 or more cells in Excel without losing data Picture 10

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:

How to combine 2 or more cells in Excel without losing data Picture 11

Similarly, performing the merging of cells for the remaining exports results:

How to combine 2 or more cells in Excel without losing data Picture 12

The above is a detailed guide on how to combine 2 or more cells in Excel without losing data. Good luck!

4.5 ★ | 2 Vote

May be interested

  • How to split strings in ExcelPhoto of How to split strings in Excel
    in excel when working with character strings, there are many cases where it is necessary to split a string to use the data, but the amount of data needs to be separated quite large, if merely manually doing it takes a lot of time and effort. strength. the following article guides to
  • How to calculate the average in ExcelPhoto of How to calculate the average in Excel
    the following article guides you in detail how to calculate the average in excel. there are 2 ways to calculate the average value: - calculate the average value according to the usual calculation formula. - calculate the average value using the average function.
  • Calculation of age from date of birth in ExcelPhoto of Calculation of age from date of birth in Excel
    the following article will guide you how to calculate the age from date of birth in excel so you can calculate the exact number of standard age in years, months and days. in the process of calculating age based on date of birth, the following cases occur: - case 1: calculates the usual age
  • Convert commas to dots in ExcelPhoto of Convert commas to dots in Excel
    by default, the number format in excel follows international standards separating thousands with commas and decimals as dots. this way of writing is in contrast to the vietnamese writing style, so this article i will guide how to change commas
  • How to paginate pages from any page in Word 2016Photo of How to paginate pages from any page in Word 2016
    the following article details how to number pages from any page in word 2016. normally when selecting page numbering, word default page numbering starts from the first page in the text.
  • How to turn off, turn on Enable Editing when opening Word, ExcelPhoto of How to turn off, turn on Enable Editing when opening Word, Excel
    instructions to turn off, turn on enable editing or protected view mode when opening word and excel files. when you download a word or excel file online, word and excel usually ask you to turn off protected view by clicking enable editing.