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, SkipEmpty As Boolean, 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 element being concatenated, but does not appear at either the beginning or end of the result string.
The
SkipEmpty parameter indicates whether empty element in the
Args array are to be skipped. If this value is
False, there may
be consecutive
Sep characters indicating empty values. For example,
=StringConcat(A1:A5) might return
1,2,,4,5 if cell
A3 is empty. If this parameter is
True, empty elements will
be skipped and there will be no consecutive delimiters. It is important to distinguish between an empty
value and a string value that contains a space character,
Chr(32). The
SkipEmpty parameter, if
True, will cause
empty values to be ignored, but text that contains only a space character will be included. The
ParamArray Args array contains the strings elements to be concatenated. Each element in the
Args array can be one of the following.
- A string within quotes or a number. For exampe, "A" or 123.
- A single cell reference. For example, A10.
- A range of cells. For example, A1:B10.
- A literal array constant. For example, {1,2,"A","B","C"}.
The following are a few examples of the StringConcat function.
Concatenating literal text:
=StringConcat("|",TRUE,"A","B","C")
returns A|B|C
Concatenating text in a range of cells:
=StringConcat("|",TRUE,B1:B5)
returns the values from B1:B5, each separated by a | character.
Concatenation In An Array Formula
=StringConcat("|",TRUE,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.
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
Dim RN As Long
Dim CN As Long
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 SkipEmpty = True Then
If R.Text <> vbNullString Then
S = S & R.Text & Sep
End If
Else
S = S & R.Text & Sep
End If
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
If SkipEmpty = True Then
If Args(N)(M) <> vbNullString Then
S = S & Args(N)(M) & Sep
End If
Else
S = S & Args(N)(M) & Sep
End If
End If
Next M
Else
For RN = LBound(Args(N), 1) To UBound(Args(N), 1)
For CN = LBound(Args(N), 2) To UBound(Args(N), 2)
If SkipEmpty = True Then
If Args(N)(RN, CN) <> vbNullString Then
S = S & Args(N)(RN, CN) & Sep
End If
Else
S = S & Args(N)(RN, CN) & Sep
End If
Next CN
Next RN
End If
Else
S = S & Args(N) & Sep
End If
Else
If SkipEmpty = True Then
If Args(N) <> vbNullString Then
S = S & Args(N) & Sep
End If
Else
S = S & Args(N) & Sep
End If
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: 14-June-2009. |