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.
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!
You should read it
- Merge cells in Excel
- How to merge cells in Excel - Instructions to merge cells in Excel 2010, 2013, 2016
- How to merge cells in Excel 2003 2007
- How to combine 2 columns Full name in Excel does not lose content
- How to color formula cells in Excel automatically
- Complete tutorial of Excel 2016 (Part 5): Basics of cells and ranges
- How to Lock Cells in Excel
- How to Sort Cells Alphabetically in Excel
May be interested
- How to split strings in Excelin 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 Excelthe 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 Excelthe 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 Excelby 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 2016the 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, Excelinstructions 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.