Find xth occurrence of a specifiecd character in a string

[Jarryd]

Hi,

Is there a function that you can use to find the position of the specified
occurrence of a specified character in a string in VBA?

For example, I want to find the position of the third (occurrence) comma
(character) in a string:
function("this is my string", ",", 3)
Result = 11

TIA,

Jarryd

 

[Jarryd]

Found this on the web.
http://www.access-programmers.co.uk/forums/showthread.php?t=156440. Some
genius created it. Come on MS, build this in as a standard. It's something
that is obviously useful.

All you need to do is Alt-F11, insert a Module and save this code to it
Hope it helps:
Public Function fFindNthOccur(ByVal pStr As String, _
pFind As String, _
pNth As Integer) As Integer

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

 

[XPS350]

Hi,

Is there a function that you can use to find the position of the specified
occurrence of a specified character in a string in VBA?

For example, I want to find the position of the third (occurrence) comma
(character) in a string:
function("this is my string", ",", 3)
Result = 11

TIA,

Jarryd

As far as I know there is no such function. But you can build your
own. It would look like:

Function FindPosition(InString, Character As String, Occurence As
Byte) As Variant
Dim PosCount As Byte
Dim OccCount As Byte
Dim TempPos As Byte

OccCount = 0
TempPos = 0

For PosCount = 1 To Len(InString)
If Mid(InString, PosCount, 1) = Character Then
OccCount = OccCount + 1
If OccCount = Occurence Then
TempPos = PosCount
End If
End If
Next

If TempPos = 0 Then
FindPosition = Null
Else
FindPosition = TempPos
End If
End Function


Groeten,

Peter
http://access.xps350.com

 

[Jarryd]

Hi Groetenm

You too possess the magic of the genius. As i said though I found a
function that worked very well.

But thanks anyway.

Kind regards,

Jarryd