Sometimes in our daily work we need to move texts from multiple cells into one cell for some reason. If we want to split texts from one cell to multiple cells, we can use Text to Columns function, so if we want to combine texts from multiple cells into one single cell, how can we do? This article will show you two ways to implement the requirement, and you can select one of them as you like.

1. Move Texts from Multiple Cells into One Cell by Formula Concatenate

Prepare a table with texts or numbers in different cells, see screenshot below:

Step 1: Select a cell to save the texts from multiple cells, for example D2, then in D2 enter

=CONCATENATE(A1,":",A2,":",B2,",",A3,":",B3,",",A4,":",B4).

Step 2: Click Enter to get result. Verify that texts are moved into D2 properly.

Notes:

1. In above case, A1, A2… are the cells you want to copy the values from them into one single cell;

2. We can find that we add “:” or “,” into the formula, actually there is no punctuation like comma or colon exists in cells before, but they are added into D2 finally, that’s because we use double quote to make them as the separators for each cell. You can add separator per your preference.

2. Move Texts from Multiple Cells into One Cell by Clipboard

We can use clipboard as the temporary media to save the texts from all cells for a short time. Then copy the texts in clipboard to a single cell to implement our requirement. See steps below:

Step1: We need to enable the Clipboard. In Home->Clipboard panel, click arrow key to expand Clipboard.

Clipboard in excel:

Step2: Select the range you want to move the texts from them into one single cell, in this case we select A1:B4, press Ctrl + C to copy them. You can find that texts are moved to Clipboard properly.

Step3: Select the destination cell to save the texts, for example D2, then double click the cell to locate cursor into the cell.

Step4: Press Ctrl + V or click on the texts in Clipboard (Click an Item to Paste) in this cell. Then All texts are saved in D2 properly.

Notes:

• By this way the separator is ‘space’ for each cell.

• The final behaviors are different by above two ways. So you can select one of them based on the behavior.

3. Move Texts from Multiple Cells into One Cell Using VBA Code

Now, for the third method, let’s delve into the skill of VBA with interactive prompts, providing a dynamic and user-friendly approach to consolidate text

Press ‘Alt + F11‘ to open the Visual Basic for Applications editor.

In the editor, click ‘Insert’ and choose ‘Module’ to create a new module.

Copy and paste the following code into the module:

Sub MergeText()
    Dim sourceRange As Range
    Dim destinationCell As Range
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the source range:", Type:=8)
    Set destinationCell = Application.InputBox("Select the destination cell:", Type:=8)
    On Error GoTo 0
    
    If Not (sourceRange Is Nothing) And Not (destinationCell Is Nothing) Then
        Dim cell As Range
        Dim mergedText As String
        For Each cell In sourceRange
            mergedText = mergedText & cell.Value & " "
        Next cell
        destinationCell.Value = Trim(mergedText)
    End If
End Sub

Close the VBA editor.

Press ‘Alt + F8‘ to open the ‘Macro’ dialog box.

Choose ‘MergeText’ from the list and  click ‘Run‘.

Follow the prompts to select the source range containing the text you want to merge

Click Ok button. Select the destination cell where you want the merged text.

Click Ok button. The VBA code will automatically merge the selected text into the chosen destination cell. Your consolidation is complete!

4. Video: Move Texts from Multiple Cells into One Cell

This Excel video tutorial where we’ll explore three distinct methods for consolidating text from multiple cells into one. In this session, we’ll cover a formula-based approach using Concatenate, the Clipboard feature, and a VBA code with prompts to select the source range and destination cell.