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

Quick View to Concatenate Range in Excel

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)

Quick View to Concatenate Range in Excel


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.

Date Set to Concatenate Range in Excel

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)

Concatenate Range Using the TEXTJOIN Function in Excel

  • 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

VBA Code to Concatenate Range in Excel

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)

Concatenate Range in Excel Using VBA Function

  • 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.

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo