Cells merging without loss of text

Suppose we have many cells which we need to merge together with text. In standard Excel set exist similar function “Merge and move in the center” but while combining it leave only text from upper-left cell. So we need to write own macro for this purposes.

For this open Visual Basic Editor (Alt+F11), insert the new blank module (Insert - Module) and copy there following code:

Sub MergeCell()
    Const sDELIM As String = " "
    Dim rCell As Range
    Dim sMergeStr As String
    If TypeName(Selection) <> "Range" Then Exit Sub
    With Selection
        For Each rCell In .Cells
            sMergeStr = sMergeStr & sDELIM & rCell.Text
        Next rCell
        Application.DisplayAlerts = False
        .Merge Across:=False
        Application.DisplayAlerts = True
        .Item(1).Value = Mid(sMergeStr, 1 + Len(sDELIM))
    End With
End Sub

Save and return to Excel. Now select required cells and run our macro (Alt+F8) (name of macro: "MergeCell").