troubleshooting Question

Parse text field into multiple values using multiple delimiters

Avatar of Dumb_Blonde
Dumb_BlondeFlag for United States of America asked on
Microsoft Access
8 Comments1 Solution1001 ViewsLast Modified:
I'm trying to split a long text field into multiple values using multiple delimiters. I found what I thought was the perfect solution here on EE posted by Alain Bryden (see below). I copied the code into a module in my DB. Using the function as written below was causing an Undefined function error. At the suggestion of an Expert, I made a few changes (in bold below) and was able to execute the function. The result returned is the value before the first delimiter. I was expecting it to return each delimited value in the string.

I'm just a business anlayst who's gotten stuck with trying to figure out how to do something using Access and VBA. I know I'm working with an array here and am wondering if I should be using something like GetRows. Unfortunately, I haven't the foggiest idea how to use it.

I'm at a loss here and rapidly running up against a deadline. Any help or suggestions you can offer would be much appreciated.

Function SplitMultiDelims(ByRef Text As String, ByRef DelimChars As String, _
        Optional ByVal IgnoreConsecutiveDelimiters As Boolean = False, _
        Optional ByVal Limit As Long = -1) As String()

Public 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 SplitMultiDelims = Arr(0)
        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 SplitMultiDelims = Arr(0)
End Function
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros