How to merge rows in excel without losing data

Answer

Merging rows in Excel without losing data requires combining the contents of multiple cells into a single cell rather than using the standard Merge & Center option, which deletes all but the top-left cell content.

Method 1: Using the CONCATENATE or CONCAT function

1. Select a blank cell where you want the merged data to appear.

2. Enter the formula using CONCAT (modern Excel) or CONCATENATE (older Excel):  

   =CONCAT(A1, " ", A2, " ", A3)

• Replace A1, A2, A3 with the cells you want to merge.

• The " " adds a space between values; replace with a comma or any separator if needed.

3. Press Enter. The merged text appears in the selected cell.

4. Copy the formula result and paste it as Values to retain the text without the formula.

Method 2: Using the TEXTJOIN function (Excel 2016 and later)

1. Select a blank cell.

2. Enter the formula: 

   =TEXTJOIN(" ", TRUE, A1:A3)

• " " is the delimiter between cell contents.

• TRUE ignores empty cells.

• A1:A3 is the range of cells to merge.

3. Press Enter. The merged data appears in one cell.

4. Copy and paste as Values to make the merged text permanent.

Method 3: Using a simple VBA macro

1. Press Alt + F11 to open the VBA editor.

2. Go to Insert → Module and paste the following code:

   Sub MergeRowsKeepData()

       Dim rng As Range, cell As Range

       Dim mergedText As String

       Set rng = Selection

       mergedText = ""

       For Each cell In rng

           If cell.Value <> "" Then

               mergedText = mergedText & cell.Value & " "

           End If

       Next cell

       rng.Cells(1, 1).Value = Trim(mergedText)

   End Sub

3. Close the editor.

4. Select the rows you want to merge.

5. Press Alt + F8, select MergeRowsKeepData, and click Run.

This macro combines all selected cells into the top-left cell while keeping all data.

Notes

• Avoid using the default Merge & Center for merging cells with data. It will keep only the top-left cell content.

• Using formulas or VBA ensures no data is lost and allows flexible separators like spaces, commas, or line breaks (CHAR(10)).

• To insert a line break between merged data using TEXTJOIN, use:   

  =TEXTJOIN(CHAR(10), TRUE, A1:A3)

• After using CHAR(10), enable Wrap Text in the cell to display each entry on a new line.

This approach keeps your spreadsheet data intact and readable.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report