How to merge cells in Excel with no data loss

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.

How to merge cells in Excel with no data loss Picture 1

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.

How to merge cells in Excel with no data loss Picture 2

Step 2 : Select the Home tab -> Fill -> Justify.

How to merge cells in Excel with no data loss Picture 3

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.

How to merge cells in Excel with no data loss Picture 4

So you've merged the cell without losing data.

How to merge cells in Excel with no data loss Picture 5

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/

How to merge cells in Excel with no data loss Picture 6

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 .

How to merge cells in Excel with no data loss Picture 7

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 .

How to merge cells in Excel with no data loss Picture 8

Step 3 : Select the cells to merge data and select Merge & Center or Merge Cell option to combine cells in Excel.

How to merge cells in Excel with no data loss Picture 9

A small message appears and select OK so the cells will be merged without data loss.

How to merge cells in Excel with no data loss Picture 10

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 .

How to merge cells in Excel with no data loss Picture 11

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 .

How to merge cells in Excel with no data loss Picture 12

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.

How to merge cells in Excel with no data loss Picture 13

A small message appears and click OK , so the cells you select will be merged quickly.

How to merge cells in Excel with no data loss Picture 14

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 ).

How to merge cells in Excel with no data loss Picture 15

The Macro dialog box appears, double click the name of the macro you just created to run the macro.

How to merge cells in Excel with no data loss Picture 16

Thus, the cells you have selected are quickly merged without losing data.

How to merge cells in Excel with no data loss Picture 17

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.