How to Concatenate Range in Excel (For Both Old and New Versions)

One of the most important features of Excel is to concatenate the values from all the cells of a range into a single cell.

Today I will be showing how to concatenate a range in Excel.

How to Concatenate Range in Excel (Quick View)

Download Practice Workbook

How to Concatenate Range in Excel

Here we’ve got a data set with the Product ID and Product Name of some products of a company named Mars Group.

Our objective today is to concatenate the names of all the products in a single cell.

Method 1: Concatenate Range Using the TEXTJOIN Function (For New Versions of Excel)

We can concatenate a range using the TEXTJOIN function of Excel. But this function is available only in Office 365.

The Syntax of the TEXTJOIN function is:

To concatenate all the Product Names with commas(,), the formula will be:

• Here I have used the delimiter as the comma(,). You use anything that you like.
• I set the ignore_blank as TRUE, to exclude the blank cells. You use it according to your need.
• And my range is the Product Names (C4:C13). You use your one.

2. Concatenate Range Using VBA Function (For Old Versions of Excel)

Those who don’t have an Office 365 subscription, can use this VBA code to concatenate range in Excel.

Open the VBA window (press Alt + F11) and write this code in a new module (Insert>Module in the upper left corner).

Code:

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

Then save the file as an Excel Macro-Enabled File.

This code produces a function called TEXTJOIN2, the same as the TEXTJOIN function discussed earlier.

The syntax of the function is:

Therefore, the formula to concatenate the Product Names into a single cell will be:

• Here I have used the delimiter as the comma(,). You use anything that you like.
• I set the ignore_blank as TRUE, to exclude the blank cells. You use it according to your needs.
• And my range is the Product Names (C4:C13). You use your one.

Conclusion

Using these methods, you can concatenate a range into a single cell in both the new and old versions of Excel. Do you have any questions? Feel free to ask us.