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.
You should read it
- Merge cells in Excel
- How to split cells, combine 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 merge cells in Google Sheets
- How to combine 2 or more cells in Excel without losing data
- Merge keyboard shortcuts in Excel
- 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
- How to merge and split cells in Word
May be interested
How to move photos, change the image position in Word
How to convert uppercase to lowercase in Excel simple and fast
How to use the IF function with VLOOKUP (examples and how to)
How to print a PDF file - The easiest way to print a PDF document
How to insert and copy PDF files into Word quickly and standardly
What is a scan? What are photo scans and document scans?