String Concatenation
This page describes a VBA Function that you can use to concatenate string values
in an array formula.
The term concatenate refers to the operation of combining two or more strings into a single
string. For example, the concatenation of abc and def is abcdef. Excel provides
a function named CONCATENATE that will concatenate a series of strings. This
function suffers from several very significant deficiencies, making is an essentially useless function. First,
you must supply each text value to CONCATENATE; you cannot pass a range
to CONCATENATE and have it concatenate all the elements in the range. A second
definciency is that CONCATENATE provides no way to automatically include a
separator character between each of the concatenated strings. Finally, CONCATENATE cannot
be used in array formulas (see Array Formulas for more information about
array formulas) to build a string based on conditional tests.
Given these deficiencies of the CONCATENATE function, you can just as well
use the concatenation operator &. There is no advantage to using CONCATENATE.
In order to overcome these deficiencies of the CONCATENATE function, it is necessary to build our
own function written in VBA that will address the problems of CONCATENATE. The rest of this page
describes such a function named StringConcat. This function overcomes all of the deficiencies of
CONCATENATE. It can be used to concatenate individual string values, the values one or more
worksheet ranges, literal arrays, and the results of an array formula operation.
The function declaration of StringConcat is as follows:
Function StringConcat(Sep As String, ParamArray Args()) As String
The
Sep parameter is a character or characters that separate the strings being concatenated. This
may be 0 or more characters. The
Sep parameter is required. If you do not want any separators in the
result string, use an empty string for the value of
Sep. The
Sep value
appears between each string being concatenated, but does not appear at either the beginning or end of the result string.
The
ParamArray Args parameter is a series values to be concatenated. Each element in the
ParamArray may be any of the following:
- A literal string, such as "A"
- A range of cells, specified either by address or by a Range Name. When elements of a two dimensional range
are concatenated, the order of concatenation is across one row then down to the next row.
- A literal array. For example, {"A","B","C"} or {"A";"B";"C"}
There is a limitation within VBA that the input strings to the StringConcat function must be less than
256 characters. The result string created by the function may be longer than 256 characters, but no individual input string may
be longer than 256 characters. If an input string exceeds 255 characters, you can have the function return a #VALUE
error or skip the offending element and return a result without that string. See the code comments in the
STRING TOO LONG HANDLING comment box for instructions on how to use On Error GoTo ContinueLoop
or On Error GoTo ErrH.
The following are a few examples of the StringConcat function.
Concatenating literal text:
=StringConcat("|","A","B","C")
returns A|B|C
Concatenating text in a range of cells:
=StringConcat("|",B1:B5)
returns the values from B1:B5, each separated by a | character.
Concatenation In An Array Formula (entered with CTRL SHIFT ENTER)
=StringConcat("|",IF(B30:B39>4,C30:C39,""))
returns the values from cells C30:C39, separated by a | character where
the corresponding value in the range B30:B39 is greater than 4.
The code for the StringConcat function is shown below. You can download bas module file
containing the code.
Function StringConcat(Sep As String, ParamArray Args()) As Variant
Dim S As String
Dim N As Long
Dim M As Long
Dim R As Range
Dim NumDims As Long
Dim LB As Long
Dim IsArrayAlloc As Boolean
If UBound(Args) - LBound(Args) + 1 = 0 Then
StringConcat = vbNullString
Exit Function
End If
For N = LBound(Args) To UBound(Args)
If IsObject(Args(N)) = True Then
If TypeOf Args(N) Is Excel.Range Then
For Each R In Args(N).Cells
If Len(R.Text) > 0 Then
S = S & R.Text & Sep
End If
Next R
Else
StringConcat = CVErr(xlErrValue)
Exit Function
End If
ElseIf IsArray(Args(N)) = True Then
IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
(LBound(Args(N)) <= UBound(Args(N))))
If IsArrayAlloc = True Then
NumDims = 1
On Error Resume Next
Err.Clear
NumDims = 1
Do Until Err.Number <> 0
LB = LBound(Args(N), NumDims)
If Err.Number = 0 Then
NumDims = NumDims + 1
Else
NumDims = NumDims - 1
End If
Loop
On Error GoTo 0
Err.Clear
If NumDims > 2 Then
StringConcat = CVErr(xlErrValue)
Exit Function
End If
If NumDims = 1 Then
For M = LBound(Args(N)) To UBound(Args(N))
If Args(N)(M) <> vbNullString Then
S = S & Args(N)(M) & Sep
End If
Next M
Else
On Error GoTo ContinueLoop
'On Error GoTo ErrH
Err.Clear
For M = LBound(Args(N), 1) To UBound(Args(N), 1)
If Args(N)(M, 1) <> vbNullString Then
S = S & Args(N)(M, 1) & Sep
End If
Next M
Err.Clear
M = LBound(Args(N), 2)
If Err.Number = 0 Then
For M = LBound(Args(N), 2) To UBound(Args(N), 2)
If Args(N)(M, 2) <> vbNullString Then
S = S & Args(N)(M, 2) & Sep
End If
Next M
End If
On Error GoTo ErrH:
End If
Else
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
End If
Else
On Error Resume Next
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
On Error GoTo 0
End If
ContinueLoop:
Next N
If Len(Sep) > 0 Then
If Len(S) > 0 Then
S = Left(S, Len(S) - Len(Sep))
End If
End If
StringConcat = S
Exit Function
ErrH:
StringConcat = CVErr(xlErrValue)
End Function
 |
This page last updated: 28-Oct-2008. |