If we have data displays in multiple rows but one column, and we want to combine them into one single cell, normal copy and paste function doesn’t work. Except copy cells into one cell one by one, is there any other good idea? Actually, we can combine texts in multiple rows into one cell by formula, just use the combination of some functions, it can be implemented easily. This free tutorial will show you the formula and you just need to follow below steps to learn the way to combine rows.

Precondition:

See screenshot below. I entered some words in multiple rows. Now I want to move them into one cell.

1. Combine Multiple Rows into One Cell by Formula

Step 1: Select one cell to output the sentence combined with the words in rows. For example, select B1, then enter the formula =TRANSPOSE(A1:A7), then press F9. Verify that ={“I love fruit:”,”Apple”,”Pear”,”Orange”,”Mango”,”Grape”,”Banana”} is displayed in formula bar.

After pressing F9:

Step 2: In the formula bar, update formula:

 =CONCATENATE("I love fruit:","Apple","Pear","Orange","Mango","Grape","Banana").

Based on previous formula, add CONCATENATE function, and remove curly brackets.

Step 3: Press Enter to check result.

Verify that words in different rows are combined into one cell properly, but obviously there is no separator between each word. If you want to separate them by space, you have to add “ “ between each word. You have to update formula:

=CONCATENATE("I love fruit:"," ","Apple"," ","Pear"," ", "Orange"," ","Mango"," ","Grape"," ","Banana")

2. Combine Texts in Multiple Rows into One Cell using VBA

Now, let’s move on to the second method, which uses a VBA macro. This method is particularly useful if you’re working with large datasets or if you need more control over the process.

To get started, press ALT + F11 to open the Visual Basic for Applications editor. This is where we’ll write our macro to automate the text combination.

In the VBA editor, go to Insert, then select Module. This will create a new module in your workbook where you can write your VBA code.

Now, let’s write a VBA macro that will loop through the selected range, concatenate the text from each cell, and then paste the combined text into a specified output cell.

Sub CombineTextFromRowsWithPrompt()

    Dim sourceRange As Range
    Dim destinationCell As Range
    Dim combinedText As String
    Dim cell As Range

    ' Prompt the user to select the source range
    Set sourceRange = Application.InputBox("Select the source range of cells", Type:=8)

    ' Check if a range was selected
    If sourceRange Is Nothing Then
        MsgBox "No source range was selected.", vbExclamation
        Exit Sub
    End If

    ' Ensure that the selected range is within the currently active worksheet
    If Not sourceRange.Worksheet Is [ActiveSheet] Then
        MsgBox "The selected source range is not on the current worksheet.", vbExclamation
        Exit Sub
    End If

    ' Prompt the user to select the destination cell
    Set destinationCell = Application.InputBox("Select the destination cell", Type:=8)

    ' Check if a cell was selected
    If destinationCell Is Nothing Then
        MsgBox "No destination cell was selected.", vbExclamation
        Exit Sub
    End If

    ' Ensure that the selected destination cell is within the currently active worksheet
    If Not destinationCell.Worksheet Is [ActiveSheet] Then
        MsgBox "The selected destination cell is not on the current worksheet.", vbExclamation
        Exit Sub
    End If

    ' Initialize an empty string for the combined text
    combinedText = ""

    ' Loop through each cell in the source range and build the combined text
    For Each cell In sourceRange
        combinedText = combinedText & cell.Value & " " ' Adds space as separator
    Next cell

    ' Trim the last separator (space) from the combined text
    combinedText = Trim(combinedText)

    ' Place the combined text into the destination cell
    destinationCell.Value = combinedText

End Sub

Press F5 to run the macro, or close the VBA editor and use the “Macro” button in the “View” tab of Excel to run the “CombineTextFromRowsWithPrompt” macro.

When prompted, select the source range of cells and the destination cell where you want the combined text to appear.

After running the macro, you’ll see that all the text from the selected range has been combined into the specified output cell. This method provides a quick and efficient way to consolidate text from multiple rows.

3. Video: Combine Texts in Multiple Rows

This Excel video tutorial we’re going to learn how to combine texts from multiple rows into a single cell using two different methods: Excel formulas and VBA macros.