How to merge cells in excel and keep all data

Answer

Merging cells in Excel usually combines multiple cells into one. Standard merging removes all data except the upper-left cell. To merge cells while keeping all data, you must use a workaround because Excel does not provide a built-in option to retain all values automatically.

Method 1: Using the CONCATENATE or TEXTJOIN Function

1. Select a new cell where you want the merged result.

2. Enter a formula that combines the content of the cells. For example, to merge cells A1, B1, and C1:

   =CONCATENATE(A1," ",B1," ",C1)

3. Press Enter. The new cell now contains all data from the selected cells, separated by spaces.

4. Copy the result and paste as Values if you want to replace the original cells.

Alternative using TEXTJOIN (Excel 2016 and later):

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

• " " is the separator between cell values.

• TRUE ignores any empty cells.

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

Method 2: Using a VBA Macro

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

2. Go to Insert → Module.

3. Paste the following code:

   Sub MergeKeepAllData() Dim Cell As Range Dim Rng As Range Dim Result As String On Error GoTo ExitSub Set Rng = Selection For Each Cell In Rng If Cell.Value <> "" Then Result = Result & Cell.Value & " " End If Next Cell Rng.Merge Rng.Value = Trim(Result) ExitSub: End Sub

4. Close the editor and return to Excel.

5. Select the cells you want to merge.

6. Press Alt + F8, select MergeKeepAllData, and click Run.

The macro merges the selected cells and keeps all content separated by spaces.

Method 3: Manual Copy-Paste

1. Copy the content of each cell manually.

2. Paste all content into a single cell.

3. Use spaces, commas, or line breaks (press Alt + Enter) to separate the values.

4. Merge the cells normally (Home → Merge & Center) if needed.

Notes

• Standard Merge & Center under the Home tab deletes all data except the top-left cell.

• Formulas like CONCATENATE or TEXTJOIN prevent data loss without using macros.

• Macros allow automation for large datasets.

• On Mac, the VBA steps are similar, but keyboard shortcuts differ: Option + F11 instead of Alt + F11.

This approach ensures no data is lost when merging cells in Excel.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report