SplitTbl – Split a string with multiple delimiters

' A variant of the Split function, that offers the following improvements'   You can pass a list of valid delimiters to the second argument'   (however, only single-char delimiters are accepted)'   Differently from the regular Split function, consecutive occurrences'   of delimiters are ignored (in this case Split creates empty'   elements in the result array)Function SplitTbl(Text As String, Optional DelimiterTbl As String = " ") As _    String()    Dim index As Long, startIndex As Long    Dim itemCount As Long    ReDim result(10) As String        For index = 1 To Len(Text)        If InStr(DelimiterTbl, Mid$(Text, index, 1)) Then            ' we've found a delimiter            If startIndex Then                ' if we're inside an item, store it in the result array                If itemCount > UBound(result) Then                    ' make room in the array, if needed                    ReDim Preserve result(itemCount + 10) As String                End If                result(itemCount) = Mid$(Text, startIndex, index - startIndex)                itemCount = itemCount + 1                startIndex = 0            End If        Else            ' this is not a delimiter            If startIndex = 0 Then                ' if we aren't in the middle of an item,                ' mark the beginning of this new element                startIndex = index            End If        End If    Next        ' process the last word    If startIndex Then        ' if we're inside an item, store it in the result array        If itemCount > UBound(result) Then            ' make room in the array, if needed            ReDim Preserve result(itemCount + 10) As String        End If        result(itemCount) = Mid$(Text, startIndex, index - startIndex)        itemCount = itemCount + 1    End If    If itemCount > 0 Then        ' assign the result only if there is at least on item        ReDim Preserve result(itemCount - 1) As String        SplitTbl = result()    Else        ' this returns the type of uninitialized array that        ' would be returned by the Split function        SplitTbl = Split("")    End IfEnd Function

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: