Normally, you often use Merge & Center directly to merge cells in Excel, but cells with data, you will lose data in the back cells because Merge Cell only retains data in the first cell. If you want to merge cells in Excel without losing data, please join us to learn the ways that the tutorial tutorial below.
Here are instructions 3 ways to merge cells in Excel without losing data, please follow along.
Method 1: Merge cells in columns using Justify and Merge & Center
If you want to combine cells in columns, then you use this way, if the cells you need to combine in multiple columns, this way does not work, please refer to the following methods. To combine cells in columns using Justify and Merge & Center, do the following:
Step 1 : Select the cells in the column that you want to merge without losing data and then resize the column width so that the cell contains all the data in the cell.
Step 2 : Select the Home tab -> Fill -> Justify.
Step 3 : After selecting Justify , all data in the cells will be transferred to the first cell. Next, you select the cells you want to merge and select Merge & Center if you want to merge cells and align data between cells or you choose Merge Cells if you want to merge cells and not align the data position after merging.
So you've merged the cell without losing data.
Method 2: Merge cells using Concatenate function and Merge & Center feature
Use the Concatenate function to combine data in cells to merge, then you use Merge & Center to merge cells. You do the following:
Step 1 : Select any cell then enter the function = CONCATENATE (C7; ''; D7; ''; E7) where C7, D7, E7 are the cells to be combined. Thus the data in the cells to be combined are displayed in the cell that you use the Concatenate function.
If you do not understand Concatenate function, you can find out here http://TipsMake.vn/cach-noi-chuoi-trong-excel/
Step 2 : Copy the data in the cell using functions and paste data into the first cell in the cells to merge.
Note: you only paste the data ( Value ) but not the Concatenate function formula by copying the data in the cell containing the function, then you right-click the first cell in the cells to combine and select Value in the Paste Options .
So the data of the cells to be merged is displayed in the first cell of the cells to be merged, next you delete the cell containing the Concatenate function .
Step 3 : Select the cells to merge data and select Merge & Center or Merge Cell option to combine cells in Excel.
A small message appears and select OK so the cells will be merged without data loss.
Method 3: Use VBA code
Here's how to help you to merge cells in Excel without losing data quickly, you can do the following:
Step 1 : First, you select the cells to merge without losing data, next you choose View -> Macros (combination Alt + F8 ) to open the Macro window .
Step 2 : In the Macro dialog box, enter a name for the unmarked macro and select Create to open the Visual Basic for Applications window .
Step 3 : Visual Basic for Applications window appears, copy the code below paste into the Sub (named macro just) . End Sub.
Dim Cll As Range, Temp As String On Error Resume Next If Selection.MergeCells = False Then For Each Cll In Selection If Cll <> "" Then Temp = Temp + Cll.Text + "" Next Cll Selection.Merge Selection.Value = Left (Temp, Len (Temp) - 1) Else Selection.UnMerge End If Selection.HorizontalAlignment = xlCenter Selection.VerticalAlignment = xlCenter
Then click the Run icon to run the lumped macro without data loss.
A small message appears and click OK , so the cells you select will be merged quickly.
If in the same Excel file you want to merge other cells, you just need to select the cells to merge and then press Alt + F8 (or choose View -> Macros ).
The Macro dialog box appears, double click the name of the macro you just created to run the macro.
Thus, the cells you have selected are quickly merged without losing data.
Above the article has detailed instructions on how to merge cells in Excel without losing data, hopefully through this article you will understand and can easily apply the ways to combine cells in Excel to keep the data. Good luck.