An Enhanced Replacement For VBA's Split Function

This page describes code that enhances the VBA Split function.

The SplitEx Function

VBA provides a very useful function called Split that transforms a delimited string into an array. The SplitEx function builds on Split to provide considerably more flexibility in how a string should be split. The function declaration of SplitEx is

Function SplitEx(InString As String, _
        Delimiter As String, _
        Optional GroupChar As String = vbNullString, _
        Optional IgnoreConsecutiveDelimiters As Boolean = False, _
        Optional Escape As String = vbNullString, _
        Optional RemoveEscape As Boolean = True, _
        Optional DeleteGroupCharacters As Boolean = False) As String()

If all optional parameters are omitted, SplitEx performs just like VBA's Split function. The options provide SplitEx

InStringg is the initial string to be split.

Delimiter is the character used to split InString.

GroupChar specifies a character which prevents splitting on a delimiter that falls between two instances of GroupChar. For example, if InString is abc "def ghi" xyz and GroupChar is a double quote, Chr(34), SplitEx will keep "def ghi"GroupCharr characters.

IgnoreConsecutiveDelimiters indicates that SplitEx is to ignore two consecutive delimiters with no text between them For example, if Delimiter is | and InString is abc|def||ghi, the normal results is four elements: abc , def , (empty) , and ghi. If IgnoreConsecutiveDelimiters is True the result does not include the (empty) element between the two consecutive delimiters.

EscapeSplitExx to ignore that delimiter. For example, if InString is abc|def\|ghi|xyz ,the delimiter | between def and ghi is not treated as a delimiter and the text def\|ghi is the complete second element of the result.

RemoveEscape indicates whether SplitEx should remove the escape character from the output.

DeleteGroupCharacters indicates whether to delete GroupChar from the final results.

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

The SplitEx Code

The following is the complete code of the SplitEx function.

Function SplitEx(InString As String, _
        Delimiter As String, _
        Optional GroupChar As String = vbNullString, _
        Optional IgnoreConsecutiveDelimiters As Boolean = False, _
        Optional Escape As String = vbNullString, _
        Optional RemoveEscape As Boolean = True, _
        Optional DeleteGroupCharacters As Boolean = False) As String()
'====================================================================================
' SplitEx
' By Chip Pearson, chip@cpearson.com , www.cpearson.com, www.cpearson.com/Excel/Split.aspx
'
' SplitEx is an extension to the standard VBA Split method. If all the optional
' parameters are omitted, SplitEx works just like Split.
'
' SplitEx provides the following advantages of the standard Split method:
'
'   GroupChar: This specifies a character that is used to delimit a range
'   in the input string within which any delimiter characters are to be ignored.
'   Most commonly, this is used to prevent SplitEx from splitting on a space
'   character that occurs within a quoted string. For example,
'       InputString = Hello "big world" from VB
'   If you set the GroupChar to Chr(34), the space within the quoted string
'   will not cause a split. The second element will be "big world" (with the quotes).
'
'   IgnoreConsecutiveDelimiters: This tells SplitEx to ignore consecutive delimiters
'   and treat them as a single delimiter. For example,
'       InputString = Hello|World||From VBA
'   SplitEx treats the consecutive delimiters || after 'World' as a single
'   delimter, so the string is split as if it were Hello|World|From VBA
'
'   Escape: This escapes a delimiter, so that it will not be used by Split. For
'   example,
'       InputString = Hello|Big\|World gets split into two components, not three.
'   The | delimiter that follows the \ escape character is not used by the split.
'   The second element is the text Big\|World
'
'   RemoveEscape: This causes the code to remove the escape character from the
'   final Split. For example, let Escape = '\' and InString is 'Hello|Big\|World'
'   If RemoveEscape is False, the second element is 'Big\|World'. If RemoveEscape
'   is True, the second element is 'Big|World', with the \ character removed.
'
'   DeleteGroupCharacters: If True, all instances of GroupChar are removed from
'   the output. Otherwse, the GroupChar remains.
'
' Results:
'   Normal: An array of strings that were split apart in the manner prescribed by
'       the various options.
'   If InString is an empty string, an uninitialized array is returned. Test this with
'       code like:
'           Dim SS() As String
'           SS = SplitEx(...)
'           If IsError(LBound(SS)) = True Then
'               ' uninitialized array. InString was empty
'           End If
'   If Delimiter is an empty string, the result is an array of one element that
'   contains InString.
'====================================================================================
Dim InGroup As Boolean
Dim Arr() As String
Dim N As Long
Dim InGroupReplace As String
Dim S As String
Dim Done As Boolean
Dim M As Long
Dim EscapeReplace As String

'
' In the input string is empty, return the
' unallocated array.
'
If InString = vbNullString Then
    SplitEx = Arr
    Exit Function
End If

'
' If the delimiter is empty, return a single
' element array containing the input string.
'
If Len(Delimiter) = 0 Then
    ReDim Arr(0 To 0)
    Arr(0) = InString
    Exit Function
End If

S = InString
N = 1
Done = False
'
' Find a character that is not used in InString. This character
' will be used to replace Delimiter when Delimiter occurs with
' a group of characters delimited by GroupChar.
Do Until Done
    If StrComp(Chr(N), Delimiter, vbBinaryCompare) <> 0 Then
        M = InStr(1, S, Chr(N), vbBinaryCompare)
        If M = 0 Then
            InGroupReplace = Chr(N)
            Done = True
        End If
    End If
    N = N + 1
Loop
InGroupReplace = Chr(N)
N = N + 1
Done = False
'
' Find a character not used in InString that we can
' use to mark an escaped delimter (an escaped delimiter
' is a delimiter than isn't used by the Split function).
If Escape <> vbNullString Then
    Do Until Done
        If StrComp(Chr(N), Escape, vbTextCompare) <> 0 Then
            M = InStr(1, S, Chr(N), vbBinaryCompare)
            If M = 0 Then
                EscapeReplace = Chr(N)
                Done = True
            End If
        End If
        N = N + 1
    Loop
End If
    
'
' Replace existing escaped delimiters with the EscapeReplace
' character.
If Escape <> vbNullString Then
    S = Replace(S, Escape & Delimiter, EscapeReplace)
End If
    
'
' If we are ignoring consecutive delimiters, replace
' consecutive delimiters with a single delimiter.
If IgnoreConsecutiveDelimiters = True Then
    N = InStr(1, S, Delimiter & Delimiter, vbBinaryCompare)
    Do Until N = 0
        S = Replace(S, Delimiter & Delimiter, Delimiter)
        N = InStr(1, S, Delimiter & Delimiter, vbBinaryCompare)
    Loop
End If

'
' Scan string and replace any delimter that occurs within
' a group sequence with InGroupReplace
If Len(GroupChar) > 0 Then
	For N = 1 To Len(S)
		If Mid(S, N, Len(GroupChar)) = GroupChar Then
			InGroup = Not InGroup
		End If
		If Mid(S, N, 1) = Delimiter Then
			If InGroup Then
				Mid(S, N, 1) = InGroupReplace
			End If
		End If
	Next N
End If

' do the split
Arr = Split(S, Delimiter)
' loop through the array and replace our special control
' characters with their original value.
For N = LBound(Arr) To UBound(Arr)
    Arr(N) = Replace(Arr(N), InGroupReplace, Delimiter)
    If DeleteGroupCharacters = True Then
        Arr(N) = Replace(Arr(N), GroupChar, vbNullString)
    End If
    If EscapeReplace <> vbNullString Then
        If RemoveEscape = True Then
            Arr(N) = Replace(Arr(N), EscapeReplace, Delimiter)
        Else
            Arr(N) = Replace(Arr(N), EscapeReplace, Escape & Delimiter)
        End If
    End If
Next N
SplitEx = Arr

End Function

This page last updated: 10-July-2010.