How to Concatenate Range in Excel (5 Useful Methods)

We’ve got a dataset with the Product ID and Product Name of some products of a company named Mars Group. The values are stored in the Cell range B5:C9. We’ll concatenate the names of all the products in a single cell.

Method 1 – Combine CONCATENATE and TRANSPOSE Functions to Concatenate a Range

Select Cell B12 and insert this formula.

=CONCATENATE(TRANSPOSE(C5:C9&",")

Select TRANSPOSE(C5:C9&”,” from the formula and press F9 on your keyboard.

The formula will convert into values like this.
Remove the Curly Brackets from both sides.

In this formula, the TRANSPOSE function converts the vertical Cell range C5:C9 into a horizontal one. Following, the CONCATENATE function combines them and converts them to a single line.

Press Enter and you will see the required output.

Note: Microsoft has changed how array formulas work in the version of Excel 365. In older versions, you need to press Ctrl + Shift + Enter to calculate an array formula.

Method 2 – Concatenate a Range with TEXTJOIN Function in Excel

The TEXTJOIN function is available only in Office 365.

Select Cell B12 and insert this formula.

=TEXTJOIN(",",TRUE,C5:C9)

Press Enter.

Note: We set the ignore_blank argument as TRUE to exclude the blank cells.

Method 3 – Apply Excel VBA to Concatenate a Range

Press Alt + F11 on your keyboard to open the Microsoft Visual Basic for Applications window.
Select Module from the Insert tab.

Insert this code inside the blank page.

Function TEXTJOIN2(delimiter As Variant, ignore_blank As Variant, range As Variant)
Dim i As Variant
Dim j As Variant
Dim out As Variant
out = ""
If ignore_blank = False Then
    For i = 1 To range.Rows.Count
        For j = 1 To range.Columns.Count
            If i = range.Rows.Count And j = range.Columns.Count Then
                out = out + range(i, j)
            Else
                out = out + range(i, j) + delimiter
            End If
        Next j
    Next i
Else
    For i = 1 To range.Rows.Count
        For j = 1 To range.Columns.Count
            If range(i, j) <> "" And i = range.Rows.Count And j = range.Columns.Count Then
                out = out + range(i, j)
            ElseIf range(i, j) <> "" Then
                out = out + range(i, j) + delimiter
            End If
        Next j
    Next i
End If
TEXTJOIN2 = out
End Function

Press Ctrl + S to save the code and close the window.

The code generates the TEXTJOIN2 function with the following syntax.

=TEXTJOIN2(delimiter,ignore_blank,range)

Use the formula in Cell B12.

=TEXTJOIN2(", ",TRUE,C5:C9)

The formula will concatenate the Product Names into a single cell.

Method 4 – Concatenate a Range with Power Query in Excel

Select Cell range C4:C9.
Go to the Data tab and select From Table/Range under the Get & Transform Data.

You will get the Create Table window with a preselected range.
Check the My table has headers box and press OK.

You will get the Power Query Editor window.
Select the strings column and go to the Transform tab.
Select Transpose from the Table group.

Select all the separated columns in the window by pressing the Ctrl button on your keyboard and right–click on any of them.
Click on Merge Columns.