sbCat

Use worksheet function TEXTJOIN from Excel 2019 onwards

"The best way to pay for a lovely moment is to enjoy it." [Richard Bach]

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

Please read my Disclaimer.

Enum mc_Macro_Categories
mcFinancial = 1
mcDate_and_Time
mcMath_and_Trig
mcStatistical
mcLookup_and_Reference
mcDatabase
mcText
mcLogical
mcInformation
mcCommands
mcCustomizing
mcMacro_Control
mcDDE_External
mcUser_Defined
mcFirst_custom_category
mcSecond_custom_category 'and so on
End Enum 'mc_Macro_Categories
Function sbCat(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 V1.0 04-Oct-2010
Dim v, s As String
For Each v In vP
If Not (bNonEmpty And v = "") Then
sbCat = sbCat & s & v
s = sDel
End If
Next v
End Function
Sub DescribeFunction()
Dim FuncName As String
Dim FuncDesc As String
Dim Category As String
Dim ArgDesc(1 To 3) As String
FuncName = "sbCat"
FuncDesc = "Concatenates all elements of a range or array vP " & _
"with delimiter sDel. If bNonEmpty is True then only " & _
"non-empty cells will be concatenated"
Category = mcText
ArgDesc(1) = "Range or array with elements to be concatenated"
ArgDesc(2) = "Delimiter [Optional, pre-set as ',']"
ArgDesc(3) = "Boolean value [Optional], True: concatenate only non-empty elements, " & _
"False: concatenate all elements"
Application.MacroOptions _
Macro:=FuncName, _
Description:=FuncDesc, _
Category:=Category, _
ArgumentDescriptions:=ArgDesc
End Sub

As a nice exercise you can compare this to Chip Pearson's StringConcat function.