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 essentailly 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
separater 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"}
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
=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 String
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
S = S & R.Text & Sep
Next R
Else
StringConcat = CVErr(xlErrValue)
Exit Function
End If
ElseIf IsArray(Args(N)) = True Then
On Error Resume Next
IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
(LBound(Args(N)) <= UBound(Args(N))))
On Error GoTo 0
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
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
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
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
Else
S = S & Args(N) & Sep
End If
Else
S = S & Args(N) & Sep
End If
Next N
If Len(Sep) > 0 Then
S = Left(S, Len(S) - Len(Sep))
End If
StringConcat = S
End Function
This page last updated: 19-October-2007