Merge cells without losing text

Description

Suppose we have many cells that we need to merge together with text. In the standard set Excel there is a similar function: "Merge and Center" , but when performing the union, it leaves only the text of the outermost cell. So we need to use our own macro for such purposes.

VBA code for the macro

To do this, open the Visual Basic editor (Alt+F11), insert the VBA module (Insert - Module) and copy the text of this procedure there:

Sub MergeToOneCell()
    'moonexcel.com.ua
    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 the required cells and run our macro (Alt+F8) (macro name: "MergeCell").