How to split a string in VBA by more than one character

user_3607273 0
Viewed 620

In C# one can easily split a split string by more than one character, one supplies an array of split characters. I was wondering what is best way to achieve this in VBA. I use VBA.Split typically but to split on more than one characters requires drilling in to the results and sub-splitting the elements. Then one has to re-dimension arrays etc. Quite painful.

Contraints

VBA responses only please. You may use .NET collection classes if you wish (yes they are creatable and callable in VBA). You may use JSON, XML as vessels for the list of split segments if you wish. You may use the humble VBA.Collection class if you wish, or even a Scripting.Dictionary. You may use even a fabricated recordset if you wish.

I know full well one can write a .NET asssembly to call the .NET String.Split method and expose assembly to VBA with COM interfaces but where is the challenge in that.

vba split
2 Answers

In my attempt, I replace all the other characters with space before splitting on space. (So I cheat a little.)

Private Function SplitByMoreThanOneChars(ByVal sLine As String)
    '*
    '* Brought to you by the Excel Development Platform Blog
    '* http://exceldevelopmentplatform.blogspot.com/2018/11/
    '*
    '* Don't get excited, this splits by spaces only
    '* we fake splitting by multiple characters by replacing those characters
    '* with spaces 
    '*
    Dim vChars2 As Variant
    vChars2 = Array(" ", "<", ">", "[", "]", "(", ")", ";")

    Dim sLine2 As String
    sLine2 = sLine

    Dim lCharLoop As Long
    For lCharLoop = LBound(vChars2) To UBound(vChars2)
        Debug.Assert Len(vChars2(lCharLoop)) = 1
        sLine2 = VBA.Replace(sLine2, vChars2(lCharLoop), " ")
    Next

    SplitByMoreThanOneChars = VBA.Split(sLine2)


End Function
user_36072730

This should be fairly easy to do with a regular expression. If you match on the negation of the passed characters to split on, the matches will be the members of the output array. The upside to doing this is that the output array only needs to be sized once because you can get a count of the matches returned by the RegExp. The pattern is fairly simple to build - it boils down to something like [^abc]+ where 'a', 'b', and 'c' are the characters to split on. About the only thing that you need to do to prepare the expression is to escape a couple characters that have special meaning in that context inside a regular expression (I probably forgot some):

Private Function BuildRegexPattern(ByVal inputString As String) As String
    Dim escapeTargets() As String
    escapeTargets = VBA.Split("- ^ \ ]")

    Dim returnValue As String
    returnValue = inputString

    Dim idx As Long
    For idx = LBound(escapeTargets) To UBound(escapeTargets)
        returnValue = Replace$(returnValue, escapeTargets(idx), "\" & escapeTargets(idx))
    Next
    BuildRegexPattern = "[^" & returnValue & "]+"
End Function

Once you have the pattern, it's just a simple matter of sizing the array and iterating over the matches to assign them (plus some other special case handling, etc.):

Public Function MultiSplit(ByVal toSplit As String, Optional ByVal delimiters As String = " ") As String()
    Dim returnValue() As String

    If toSplit = vbNullString Then
        returnValue = VBA.Split(vbNullString)
    Else
        With New RegExp
            .Pattern = BuildRegexPattern(IIf(delimiters = vbNullString, " ", delimiters))
            .MultiLine = True
            .Global = True
            If Not .Test(toSplit) Then
                'Only delimiters.
                ReDim returnValue(Len(toSplit) - 1)
            Else
                Dim matches As Object
                Set matches = .Execute(toSplit)
                ReDim returnValue(matches.Count - 1)
                Dim idx As Long
                For idx = LBound(returnValue) To UBound(returnValue)
                    returnValue(idx) = matches(idx)
                Next
            End If
        End With
    End If

    MultiSplit = returnValue
End Function
user_40888520
Related