# 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.
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 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".

### Concatenate Based on a Single Criteria 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".

### Concatenate Based on Multiple Criteria 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".