There is no native formula to do that in Excel. I recommend a udf. Here's one from Harlan Grove
Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 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
So after putting the code into a module
=aconcat(A1:B2,",") would give the contents of A1,B1,A2,B2
Does that work for you?
Bookmarks