Finding the nth space or character in a string can turn ugly
in a hurry if n > 1. Multiple InStr()'s are something like nested
Iif() statements -- miss one parenthesis and the whole thing
crashes.
This function will return the nth occurence. Copy/paste to a
standard module, then call as shown in the example input/output.
You'll have to play with it a bit if the length of the selected character/
string is > 1.
in a hurry if n > 1. Multiple InStr()'s are something like nested
Iif() statements -- miss one parenthesis and the whole thing
crashes.
This function will return the nth occurence. Copy/paste to a
standard module, then call as shown in the example input/output.
You'll have to play with it a bit if the length of the selected character/
string is > 1.
Code:
Public Function fFindNthOccur(ByVal pStr As String, _
pFind As String, _
pNth As Integer) As Integer
'------------------------------------------------------------------
' Purpose: Return location of nth occurence of item in a string.
' Coded by: raskew
' Arguments: pStr: The string to be searched.
' pFind: The item to search for.
' pNth: The occurence of the item in string.
' Input: From the debug (immediate) window:
' x = "The quick brown fox jumped over the lazy dog"
' 1) ? fFindNthOccur(x, " ", 3)
' 2) ? left(x, fFindNthOccur(x, " ", 3))
' 3) ? mid(x, fFindNthOccur(x, " ", 3)+1)
' Output: 1) 16
' 2) The quick brown
' 3) fox jumped over the lazy dog
'------------------------------------------------------------------
Dim strHold As String
Dim strFind As String
Dim intHold As Integer
Dim intSay As Integer
Dim intKeep As Integer
Dim n As Integer
strHold = pStr
strFind = pFind
intHold = pNth
intKeep = 0
n = 0
Do While n < intHold
If InStr(strHold, strFind) = 0 Then
fFindNthOccur = 0
Exit Do
Else
intSay = InStr(1, strHold, strFind)
intKeep = intKeep + intSay
n = n + 1
strHold = Mid(strHold, intSay + Len(strFind))
fFindNthOccur = intKeep
End If
Loop
End Function