Requested string function


Tom Ellison

I received a request for this function, which I had offered to provide in an
earlier post here. Rather than send it just to the person who emailed me,
I'm providing it here.

Call the function with 3 parameters, none are optional. The first is a
string to be searched. The second is a string (often just one character, or
a CrLf) to search for, which I think of as the parse delimiter. The third
is an integer showing which "substring" to return.

Everything from the start of the string being searched, up to the string
that parses it, is the first parsing. Starting after that first occurance
of the delimit string is the second parsing, ending at the next occurance of
the delimit string or the end of the string.

The function is case sensitive.

For example:

"Now is the time for all good men to come to the aid of their country"

Parse this using "i"

1 -5 Now
2 -4 s the t
3 -3 me for all good men to come to the a
4 -2 d of the
5 -1 r country

What I've shown is the 5 substrings of the phrase being parsed, broken at
each occurrance of "i". They are numbered to the left from 1 to 5. If your
3rd parameter is 1 to 5 you'll get the string shown.

The funcion will also parse from the end of the string, using a negative
value for the 3rd parameter Occurrance. So, -1 is the last occurrance, -2
is the next to last, and so on.

It returns an empty string if the occurrance of the delimited string does
not exist.

Public Function Parse1(ByVal ObjectString As String, ByVal DelimitString As
String, ByVal Occurrance As Integer) As String
Dim Count As Integer, Begin As Integer, Found As Integer, DelimitLength
As Integer

DelimitLength = Len(DelimitString)
Parse1 = ""

If Occurrance < 0 Then
Count = 1
Begin = 1
Found = InStr(Begin, ObjectString, DelimitString)
If Found = 0 Then Exit Do
Begin = Found + DelimitLength
Count = Count + 1
Occurrance = Count + Occurrance + 1
End If

Count = 1
Begin = 1
Do While Count < Occurrance
Found = InStr(Begin, ObjectString, DelimitString)
If Found = 0 Then Exit Function
Begin = Found + DelimitLength
Count = Count + 1
Found = InStr(Begin, ObjectString, DelimitString)
If Found = 0 Then Found = Len(ObjectString) + 1
Parse1 = Mid(ObjectString, Begin, Found - Begin)
End Function

Enjoy!!! (this line is not part of the code!)

Tom Ellison



John Nurick

Hi Tom,

I don't think that all these results are what you intended<g>:

?parse1("1,2,3,4", ",", 0)
?parse1("1,2,3,4", ",", 1)
?parse1("1,2,3,4", ",", -4)
?parse1("1,2,3,4", ",", -5)

Here's my attempt:

Function Parse2( _
ByVal V As Variant, _
ByVal Delimiter As String, _
ByVal Index As Long) As Variant

'Treats V as a string containing a list of items separated by
'Returns the Indexth item (first item is index 0).
'Negative index counts from end of list (last item is index -1)
'Returns Null if Index points to an item that's not in the list.

Dim arItems As Variant

arItems = Split(CStr(V), Delimiter)

If Index >= 0 Then
If Index <= UBound(arItems) Then
Parse2 = arItems(Index)
Parse2 = Null
End If
If Abs(Index) <= UBound(arItems) + 1 Then
Parse2 = arItems(UBound(arItems) + Index + 1)
Parse2 = Null
End If
End If
End Function

Michel Walsh


The Split function can help:

? Split("Now is the time for all good men to come to the aid of their
country", "i") (3)
d of the

Note that the index starts at 0, not 1, so, here, 3 returns the fourth

Vanderghast, Access MVP



Tom Ellison

Hello, John,

Not sure with which results you disagree.

I never tired the function with 0 as the occurrance. Given the description
of how it should work, zero doesn't make any sense to me. Being somewhat
empirical about it, I really don't care what it returns. Not very "purist"
and maybe not a good enough way to present a function for publication, but I
was really trying to just get something working.

The results for 1 and -4 are just what I intended.

The result for -5 is wrong, of course. It should return an empty string.
Given the situation I was in when I wrote the function, this wasn't an
issue, but again, for publication, this isn't so great. I'll definitely
give you that one.

In the case of the data for which I wrote this, there was guaranteed to be
at least 3 lines of data, and the delimiter was always CrLf. I never had
any need to use negative values except -1 and -2, so having a negative value
exceeding the number of parsings available didn't come up.

I'm glad to know about this error, in case I ever need to use the function
beyond the original requirements.

The Split() function, I'm thinking, either wasn't around yet when I wrote
this (98) or I wasn't yet aware of it. Maybe Split() makes it faster, but
it's so nearly instantaneous as it is that doesn't bother me.


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question