We help IT Professionals succeed at work.
Get Started

Parse text field into multiple values using multiple delimiters

Dumb_Blonde
Dumb_Blonde asked
on
997 Views
Last Modified: 2012-05-11
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
Comment
Watch Question
CERTIFIED EXPERT
Top Expert 2010
Commented:
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE