Dumb_Blonde
asked on
Parse text field into multiple values using multiple delimiters
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 IgnoreConsecutiveDelimiter s As Boolean = False, _
Optional ByVal Limit As Long = -1) As String()
Public Function SplitMultiDelims(ByRef Text As String, ByRef DelimChars As String, _
Optional ByVal IgnoreConsecutiveDelimiter s 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 IgnoreConsecutiveDelimiter s 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 IgnoreConsecutiveDelimiter s 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
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 IgnoreConsecutiveDelimiter
Optional ByVal Limit As Long = -1) As String()
Public Function SplitMultiDelims(ByRef Text As String, ByRef DelimChars As String, _
Optional ByVal IgnoreConsecutiveDelimiter
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 IgnoreConsecutiveDelimiter
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 IgnoreConsecutiveDelimiter
ReDim Preserve Arr(0 To Elements) 'Chop off unused array elements
SplitMultiDelims = Arr SplitMultiDelims = Arr(0)
End Function
Perhaps you can post the code that you are using to CALL the above function and perhaps some test data and what you expect the function to return.
Dumb_Blonde,
Could you post a few rows of example data, along with the result you would expect based on that sample?
Patrick
Could you post a few rows of example data, along with the result you would expect based on that sample?
Patrick
ASKER
I don't have any code that I'm calling this from. I used the function in a query expression. It's now obvious to me that it won't work using it in an expression in the query designer but I'm at a loss as to how to write the VBA code to do this. I'm guessing it requires some kind of a loop? In any event I have a column in the table called Narrative. The values in that table look like this:
"HO2, 2209/2210/2689/2690/2728/2 729/2852/2 947/3159/3 160/3417/3 431/3521/3 522, Embedded, Cal Yr, CO/TO Yes"
I need the data parsed (using " ", "," and "/" as delimiters) into "HO2", "2209", "2210", "2689", "2690" etc. Ideally, each value in a seperate row but if I could get it parsed into columns in the same row I can work with it.
Thanks much.
"HO2, 2209/2210/2689/2690/2728/2
I need the data parsed (using " ", "," and "/" as delimiters) into "HO2", "2209", "2210", "2689", "2690" etc. Ideally, each value in a seperate row but if I could get it parsed into columns in the same row I can work with it.
Thanks much.
The Split VBA code (without the Bolded modifications) works fine with your test data. The problem is really how you want to use the resulting data.
You mentioned that you would want the data in a separate row - you would need a new table for this, say:
ID - Autnumber
SourceRowID - some unique value of your original data row
Element - int, that represents the position of the parsed element in your array
Data - string, the actual data
You can then call your Split function as follows
Dim str As String, strArr() As String
Dim i As int
str = "HO2, 2209/2210/2689/2690/2728/2 729/2852/2 947/3159/3 160/3417/3 431/3521/3 522, Embedded, Cal Yr, CO/TO Yes"
strArr = SplitMultiDelims(str, " ,/", True)
For i = 0 to UBound(strArr)
Debug.Print i, strArr(i)
'DoCmd.ExecuteSQL("INSERT INTO tblNew (fileds) VALUES (key, i, strArr(i))
Next i
You mentioned that you would want the data in a separate row - you would need a new table for this, say:
ID - Autnumber
SourceRowID - some unique value of your original data row
Element - int, that represents the position of the parsed element in your array
Data - string, the actual data
You can then call your Split function as follows
Dim str As String, strArr() As String
Dim i As int
str = "HO2, 2209/2210/2689/2690/2728/2
strArr = SplitMultiDelims(str, " ,/", True)
For i = 0 to UBound(strArr)
Debug.Print i, strArr(i)
'DoCmd.ExecuteSQL("INSERT INTO tblNew (fileds) VALUES (key, i, strArr(i))
Next i
ASKER
I'm getting a compile error (Cannot assign to array) on this line:
strArr = SplitMultiDelims(str, " ,/", True)
strArr = SplitMultiDelims(str, " ,/", True)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Patrick -- Thank you, thank you, thank youi! Not I was able to parse the data the way I needed but after spending some time with your article found that I could select just the values I really needed (4 digit numerics) by changing the pattern to "\d{4}".
Regards,
DB
Regards,
DB
Dumb_Blonde,
Glad to help! If you have not already done so, I would really appreciate it if you could please return to my article
https://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
and click 'Yes' for the 'Was this helpful?' voting.
Patrick
Glad to help! If you have not already done so, I would really appreciate it if you could please return to my article
https://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html
and click 'Yes' for the 'Was this helpful?' voting.
Patrick