How to merge cells in excel and keep all text

Answer

Merging cells in Excel usually combines multiple cells into one. Standard merging removes all content except the upper-left cell. To merge cells and keep all text, follow these methods.

Method 1: Using the CONCATENATE or TEXTJOIN Function

1. Select a blank cell where you want the combined text to appear.

2. Use the formula to combine the contents of multiple cells. For example, if you want to merge cells A1, B1, and C1:

For Excel 2016 and earlier:

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

For Excel 2019 and later / Excel 365:

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

• The first argument " " adds a space between cell contents.

• TRUE ignores empty cells.

3. Press Enter. The cell now contains all text from the selected cells.

4. Copy the result and paste it as values if you need to replace the original cells.

Method 2: Using Flash Fill

1. In a blank cell next to your data, type the combined text manually for the first row.

2. Start typing the second row. Excel will suggest the merged result.

3. Press Enter to accept the Flash Fill suggestion.

Method 3: Using VBA Macro (for multiple merges quickly)

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

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

   Sub MergeKeepAllText()

       Dim Rng As Range, Cell As Range

       Dim CombinedText As String

           Set Rng = Selection

       CombinedText = ""

          For Each Cell In Rng

           If Cell.Value <> "" Then

               CombinedText = CombinedText & Cell.Value & " "

           End If

       Next Cell

           Rng.Merge

       Rng.Value = Trim(CombinedText)

   End Sub

3. Close the editor.

4. Select the cells you want to merge.

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

Notes

• Standard Merge & Center removes all data except the top-left cell.

• Using formulas or VBA ensures no data is lost.

• Flash Fill works well for small ranges but not for large datasets.

This method allows keeping all text while merging, preventing accidental data loss.

Do you want me to show a method that merges cells and preserves formatting as well?

Yes · 0 No · 0 Add Comment · 0

Add ·

Report