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
- Open the workbook in which to store the code.
- Open the Visual Basic Editor (Alt+F11).
- Insert a standard module (Insert > Module).
- Copy/paste the above code into the module.
- Return to Microsoft Excel (Alt+Q).
- 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".
Sample Workbook: Download
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".
Sample Workbook: Download
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".
Sample Workbook: Download