ThreeWave String Concatenation

This page describes a VBA Function that you can use to concatenate string values in an array formula.
ShortFadeBar

Introduction To Concatenation

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.

SectionBreak

The StringConcat Function

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"}

Long Strings

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.

SectionBreak

Examples Of StringConcat

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.

download You can download the file with all the example code on this page.

SectionBreak

The StringConcat Function Code

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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' StringConcat
' By Chip Pearson, chip@cpearson.com, www.cpearson.com
'                  www.cpearson.com/Excel/stringconcatenation.aspx
' This function concatenates all the elements in the Args array,
' delimited by the Sep character, into a single string. This function
' can be used in an array formula. There is a VBA imposed limit that
' a string in a passed in array (e.g.,  calling this function from
' an array formula in a worksheet cell) must be less than 256 characters.
' See the comments at STRING TOO LONG HANDLING for details.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
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 no parameters were passed in, return
' vbNullString.
'''''''''''''''''''''''''''''''''''''''''''
If UBound(Args) - LBound(Args) + 1 = 0 Then
    StringConcat = vbNullString
    Exit Function
End If

For N = LBound(Args) To UBound(Args)
    ''''''''''''''''''''''''''''''''''''''''''''''''
    ' Loop through the Args
    ''''''''''''''''''''''''''''''''''''''''''''''''
    If IsObject(Args(N)) = True Then
        '''''''''''''''''''''''''''''''''''''
        ' OBJECT
        ' If we have an object, ensure it
        ' it a Range. The Range object
        ' is the only type of object we'll
        ' work with. Anything else causes
        ' a #VALUE error.
        ''''''''''''''''''''''''''''''''''''
        If TypeOf Args(N) Is Excel.Range Then
            '''''''''''''''''''''''''''''''''''''''''
            ' If it is a Range, loop through the
            ' cells and create append the elements
            ' to the string S.
            '''''''''''''''''''''''''''''''''''''''''
            For Each R In Args(N).Cells
                If Len(R.Text) > 0 Then
                    S = S & R.Text & Sep
                End If
            Next R
        Else
            '''''''''''''''''''''''''''''''''
            ' Unsupported object type. Return
            ' a #VALUE error.
            '''''''''''''''''''''''''''''''''
            StringConcat = CVErr(xlErrValue)
            Exit Function
        End If
    
    ElseIf IsArray(Args(N)) = True Then
        '''''''''''''''''''''''''''''''''''''
        ' ARRAY
        ' If Args(N) is an array, ensure it
        ' is an allocated array.
        '''''''''''''''''''''''''''''''''''''
        IsArrayAlloc = (Not IsError(LBound(Args(N))) And _
            (LBound(Args(N)) <= UBound(Args(N))))
        If IsArrayAlloc = True Then
            ''''''''''''''''''''''''''''''''''''
            ' The array is allocated. Determine
            ' the number of dimensions of the
            ' array.
            '''''''''''''''''''''''''''''''''''''
            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
            ''''''''''''''''''''''''''''''''''
            ' The array must have either
            ' one or two dimensions. Greater
            ' that two caues a #VALUE error.
            ''''''''''''''''''''''''''''''''''
            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
                ''''''''''''''''''''''''''''''''''''''''''''''''
                ' STRING TOO LONG HANDLING
                ' Here, the error handler must be set to either
                '   On Error GoTo ContinueLoop
                '   or
                '   On Error GoTo ErrH
                ' If you use ErrH, then any error, including
                ' a string too long error, will cause the function
                ' to return #VALUE and quit. If you use ContinueLoop,
                ' the problematic value is ignored and not included
                ' in the result, and the result is the concatenation
                ' of all non-error values in the input. This code is
                ' used in the case that an input string is longer than
                ' 255 characters.
                ''''''''''''''''''''''''''''''''''''''''''''''''
                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

'''''''''''''''''''''''''''''
' Remove the trailing Sep
'''''''''''''''''''''''''''''
If Len(Sep) > 0 Then
    If Len(S) > 0 Then
        S = Left(S, Len(S) - Len(Sep))
    End If
End If

StringConcat = S
'''''''''''''''''''''''''''''
' Success. Get out.
'''''''''''''''''''''''''''''
Exit Function
ErrH:
'''''''''''''''''''''''''''''
' Error. Return #VALUE
'''''''''''''''''''''''''''''
StringConcat = CVErr(xlErrValue)
End Function
ShortFadeBar
LastUpdate This page last updated: 28-Oct-2008.

-->