Rifat Hassan
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:
=TEXTJOIN(delimiter,ignore_blank,range)To concatenate all the Product Names with commas(,), the formula will be:
=TEXTJOIN(",",TRUE,C4:C13)
- 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.
Read more: How to Combine Multiple Cells Into One Cell Separated By Comma In Excel
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:
=TEXTJOIN2(delimiter,ignore_blank,range)Therefore, the formula to concatenate the Product Names into a single cell will be:
=TEXTJOIN2(", ",TRUE,C4:C13)
- 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.
