I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.

Information on
St. Joseph's

Donation Link

Excel©'s worksheet function CONCATENATE is not able to concatenate cell or element values of ranges or arrays. A user-defined function MultiCat can help here:

Function MultiCat(vP As Variant, _
     Optional sDel As String = ",", _
     Optional bNonEmpty As Boolean = True) As String
'Concatenate all cells in a range or array,
'delimited by sDel. If bNonEmpty is TRUE then only
'non-empty cells will be concatenated.
'Reverse ("moc.LiborPlus.www") PB V0.3 27-Mar-2009
Dim vE As Variant, s As String

For Each vE In vP
    If Not (bNonEmpty And vE = "") Then
        MultiCat = MultiCat & s & vE
        s = sDel
    End If
Next vE

End Function

You can call this function now from a worksheet with =MultiCat(A1:C3,,), for example. Or you can call it within a VBA routine like s = MultiCat({"i","concatenate","this"}," ").
I got the idea for this function from McGimpsey's page http://www.mcgimpsey.com/excel/udfs/multicat.html. But I changed the type of the input range to variant to be able to process arrays, too.

Another trickier example for the usage of MultiCat:
If you want to show all missing values of a series of unsorted (!) integers comma-delimited in one string, take
=MultiCat(IF(ISNA(MATCH(ROW(INDIRECT(MIN(A1:A99)&":"&MAX(A1:A99))),A1:A99,)),ROW(INDIRECT(MIN(A1:A99)&":"&MAX(A1:A99)))&",",""),"",FALSE)
If A1=1, A3=3 and A5=5 then the result would be 2,4,, for example.

Or you can present all positive numbers of a range A1:A6 in one string with
=MultiCat(IF(A1:A6>0,A1:A6,""))
, for example.
This formula has to be entered as an array formula: Press CTRL + SHIFT + ENTER to enter it.

Another solution is the C++ Excel addin function sbCat:

#include<cppinterface.h>
#pragma warning (disable : 4996)

using namespace std;

wstring // Concatenates cell elements
sbCat(const CellMatrix& input, // Data to concatenate
wstring delim, // Delimiter
const bool skip_empty = false // Skip empty cells if true
)
// Concatenates cells elements.
// Reverse("moc.LiborPlus.www") PB 0.11 06-Jan-2010
{
    wstring result, temp;

    for (unsigned long i=0; i < input.RowsInStructure(); ++i)
        for (unsigned long j=0; j < input.ColumnsInStructure(); ++j)
             if (input(i,j).IsEmpty()) {
                if (!skip_empty) {
                   result += temp;
                   temp = delim;
                }
             } else {
                result += temp;
                result += (wstring) input(i,j);
                temp = delim;
             }
    return result;
}

Another helpful function is ReturnNonEmpty:

Function ReturnNonEmpty(ParamArray v() As Variant) As Variant
'ReturnNonEmpty returns all non-empty cells of all
'input areas in one result array.
'Reverse("moc.LiborPlus.www") V0.4 PB 30-Sep-2009
Dim vI As Variant, vJ As Variant
Dim i As Long, lvdim As Long
lvdim = 100 'Let us start with a small dim for result array
ReDim vR(1 To lvdim) As Variant 'Result array
On Error GoTo ErrHdl
i = 0
For Each vI In v
    For Each vJ In vI
        If Len(vJ) > 0 Then
            i = i + 1
            vR(i) = vJ
        End If
    Next vJ
Next vI
On Error GoTo 0
If i < 1 Then
    ReturnNonEmpty = CVErr(xlErrValue)
    Exit Function
End If
ReDim Preserve vR(1 To i) As Variant
ReturnNonEmpty = Application.WorksheetFunction.Transpose(vR)
Exit Function
ErrHdl:
If Err.Number = 9 Then
   If i > lvdim Then
       'Here we normally get if we breach Ubound(vR)
       'So we need to increase dimension
       lvdim = 10 * lvdim
       ReDim Preserve vR(1 To lvdim)
       Err.Number = 0
       Resume 'Back to statement which caused error
   End If
End If
'Other error - terminate
On Error GoTo 0
Resume
End Function