Sulprobil
Search…
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:
Exercise Question: Why does above formula in cell C4 not work for non-positive input numbers?
Please read my Disclaimer.
1
Enum mc_Macro_Categories
2
mcFinancial = 1
3
mcDate_and_Time
4
mcMath_and_Trig
5
mcStatistical
6
mcLookup_and_Reference
7
mcDatabase
8
mcText
9
mcLogical
10
mcInformation
11
mcCommands
12
mcCustomizing
13
mcMacro_Control
14
mcDDE_External
15
mcUser_Defined
16
mcFirst_custom_category
17
mcSecond_custom_category 'and so on
18
End Enum 'mc_Macro_Categories
19
20
Function sbCat(vP As Variant, _
21
Optional sDel As String = ",", _
22
Optional bNonEmpty As Boolean = True) As String
23
'Concatenate all cells in a range or array, delimited
24
'by sDel. If bNonEmpty is TRUE then only non-empty
25
'cells will be concatenated.
26
'Reverse ("moc.LiborPlus.www") PB V1.0 04-Oct-2010
27
Dim v, s As String
28
29
For Each v In vP
30
If Not (bNonEmpty And v = "") Then
31
sbCat = sbCat & s & v
32
s = sDel
33
End If
34
Next v
35
36
End Function
37
38
Sub DescribeFunction()
39
Dim FuncName As String
40
Dim FuncDesc As String
41
Dim Category As String
42
Dim ArgDesc(1 To 3) As String
43
44
FuncName = "sbCat"
45
FuncDesc = "Concatenates all elements of a range or array vP " & _
46
"with delimiter sDel. If bNonEmpty is True then only " & _
47
"non-empty cells will be concatenated"
48
Category = mcText
49
ArgDesc(1) = "Range or array with elements to be concatenated"
50
ArgDesc(2) = "Delimiter [Optional, pre-set as ',']"
51
ArgDesc(3) = "Boolean value [Optional], True: concatenate only non-empty elements, " & _
52
"False: concatenate all elements"
53
54
Application.MacroOptions _
55
Macro:=FuncName, _
56
Description:=FuncDesc, _
57
Category:=Category, _
58
ArgumentDescriptions:=ArgDesc
59
End Sub
Copied!
As a nice exercise you can compare this to Chip Pearson's StringConcat function.
Last modified 9mo ago
Copy link