Split("Welcome to VBA", " ")
which gives you the array: ("Welcome","to","VBA").
Split("Year/Month/Day Hours:Minutes:Seconds.Milliseconds", "/ :.")
and get the result: ("Year", "Month", "Day", "Hours", "Minutes", "Seconds", "Milliseconds")
'=======================================================
'ReplaceAndSplit by alainbryden, optimized by aikimark
'Uses the native REPLACE() function to replace all delimiters with a common
'delimiter, and then splits them based on that.
'=======================================================
Function ReplaceAndSplit(ByRef Text As String, ByRef DelimChars As String) As String()
Dim DelimLen As Long, Delim As Long
Dim strTemp As String, Delim1 As String, Arr() As String, ThisDelim As String
strTemp = Text
Delim1 = Left$(DelimChars, 1)
DelimLen = Len(DelimChars)
For Delim = 2 To DelimLen
ThisDelim = Mid$(DelimChars, Delim, 1)
If InStr(strTemp, ThisDelim) <> 0 Then _
strTemp = Replace(strTemp, ThisDelim, Delim1)
Next
ReplaceAndSplit = Split(strTemp, Delim1)
End Function
SplitMultiDelims(ByRef Text As String, ByRef DelimChars As String,
Optional ByVal IgnoreConsecutiveDelimiters As Boolean = False,
Optional ByVal Limit As Long = -1) As String()
Differences between Split() and SplitMultiDelims()
Dim str As String, strArr() as String
str = "Hello, my friends. Hello,,,,Hello,Hello"
strArr = SplitMultiDelims(str, " ")
'strArr = ("Hello,", "my", "friends.", "Hello,,,,Hello,Hello")
strArr = SplitMultiDelims(str, " ,")
'strArr = ("Hello", "", "my", "friends.", "Hello", "", "", "", "Hello", "Hello")
strArr = SplitMultiDelims(str, " ,", true)
'strArr = ("Hello", "my", "friends.", "Hello", "Hello", "Hello")
strArr = SplitMultiDelims(str, " ,", true, 3)
'strArr = ("Hello", "my", "friends. Hello,,,,Hello,Hello")
Here is the code for the function:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' SplitMultiDelims by alainbryden
' This function splits Text into an array of substrings, each substring
' delimited by any character in DelimChars. Only a single character
' may be a delimiter between two substrings, but DelimChars may
' contain any number of delimiter characters. It returns a single element
' array containing all of text if DelimChars is empty, or a 1 or greater
' element array if the Text is successfully split into substrings.
' If IgnoreConsecutiveDelimiters is true, empty array elements will not occur.
' If Limit greater than 0, the function will only split Text into 'Limit'
' array elements or less. The last element will contain the rest of Text.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Function SplitMultiDelims(ByRef Text As String, ByRef DelimChars As String, _
Optional ByVal IgnoreConsecutiveDelimiters As Boolean = False, _
Optional ByVal Limit As Long = -1) As String()
Dim ElemStart As Long, N As Long, M As Long, Elements As Long
Dim lDelims As Long, lText As Long
Dim Arr() As String
lText = Len(Text)
lDelims = Len(DelimChars)
If lDelims = 0 Or lText = 0 Or Limit = 1 Then
ReDim Arr(0 To 0)
Arr(0) = Text
SplitMultiDelims = Arr
Exit Function
End If
ReDim Arr(0 To IIf(Limit = -1, lText - 1, Limit))
Elements = 0: ElemStart = 1
For N = 1 To lText
If InStr(DelimChars, Mid(Text, N, 1)) Then
Arr(Elements) = Mid(Text, ElemStart, N - ElemStart)
If IgnoreConsecutiveDelimiters Then
If Len(Arr(Elements)) > 0 Then Elements = Elements + 1
Else
Elements = Elements + 1
End If
ElemStart = N + 1
If Elements + 1 = Limit Then Exit For
End If
Next N
'Get the last token terminated by the end of the string into the array
If ElemStart <= lText Then Arr(Elements) = Mid(Text, ElemStart)
'Since the end of string counts as the terminating delimiter, if the last character
'was also a delimiter, we treat the two as consecutive, and so ignore the last elemnent
If IgnoreConsecutiveDelimiters Then If Len(Arr(Elements)) = 0 Then Elements = Elements - 1
ReDim Preserve Arr(0 To Elements) 'Chop off unused array elements
SplitMultiDelims = Arr
End Function
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (5)
Commented:
https://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1679-Passing-lists-and-complex-data-through-a-parameter.html
The new article is more about passing complex parameters, but it uses this parsing problem as its initial example.
Commented:
Commented:
Alain - You SAVED my life today.
All Hats of to you. This is miracle, cant explain :)
Thanks a MILLION Times
Commented:
If anyone needs to modify the function to INCLUDE the delimiter character in the split one only needs to change
ElemStart = N + 1
to
ElemStart = N
Commented:
Good idea that!