Concatenate the Data in a Range

The following custom function concatentates the data in a range...

Function AConcat(a As Variant, Optional Sep As String = "") As String

    'By Harlan Grove, March 2002

    Dim Y As Variant

    If TypeOf a Is Range Then
        For Each Y In a.Cells
            AConcat = AConcat & Y.Value & Sep
        Next Y
    ElseIf IsArray(a) Then
        For Each Y In a
            AConcat = AConcat & Y & Sep
        Next Y
    Else
        AConcat = AConcat & a & Sep
    End If
    
    AConcat = Left(AConcat, Len(AConcat) - Len(Sep))
    
End Function

Where to Put the Code

  1. Open the workbook in which to store the code.
  2. Open the Visual Basic Editor (Alt+F11).
  3. Insert a standard module (Insert > Module).
  4. Copy/paste the above code into the module.
  5. Return to Microsoft Excel (Alt+Q).
  6. Save the workbook.

How to Use the Custom Function

Once the code for the custom function has been copied into a standard module, the following formulas can be used in a worksheet. Note that the formulas use a comma as a delimiter. However, the delimiter can be changed as desired. For example, to use a semi-colon instead of a comma, replace each instance of "," (in red) with ";".

Concatenate Without a Criteria

Concatenate Without a Criteria Using a Custom Function

To concatenate the values in A2:A10...

=AConcat(A2:A10,",")

Based on the sample data, the formula returns "1,2,3,4,5,6,7,8,9".

Sample Workbook:  Download

Concatenate Based on a Single Criteria

Concatenate Based on a Single Criteria Using a Custom Function

To concatenate the values in B2:B10, where the corresponding value in A2:A10 equals the value in D2...

=SUBSTITUTE(AConcat(IF(A2:A10=D2,","&B2:B10,"")),",","",1)

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.

Based on the sample data, the formula returns "1,4,7".

Sample Workbook:  Download

Concatenate Based on Multiple Criteria

Concatenate Based on Multiple Criteria Using a Custom Function

To concatenate the values in C2:C10, where the corresponding value in A2:A10 equals the value in E2, and the corresponding value in B2:B10 equals the value in F2...

=SUBSTITUTE(AConcat(IF(A2:A10=E2,IF(B2:B10=F2,","&C2:C10,""),"")),",","",1)

Note that the formula needs to be confirmed with CONTROL+SHIFT+ENTER. If done correctly, Excel will automatically place curly braces {...} around the formula.

Based on the sample data, the formula returns "1,7".

Sample Workbook:  Download