Start Position of a Nth Instance of String in a piece of Text

Vishesh's picture

Code below returns the Starting position of Nth Instance of a string in a piece of text. Just paste the below code in the code module and run the 'TestIt' procedure. Alternatively download the attached file and run the same procedure from there from the module 'mod_NthInstance'.


Option Compare Text

Option Explicit


Function lngStartPosition(strSearchIn As String, strSearchString As String, lngInstance As Long)

    'This function will return 0 if Search String is not found

    Dim intLenLoop          As Integer

    Dim lngInstanceCount    As Long

    For intLenLoop = 1 To Len(strSearchIn)

        lngStartPosition = lngStartPosition + 1

        If Mid(strSearchIn, intLenLoop, Len(strSearchString)) = strSearchString Then

            lngInstanceCount = lngInstanceCount + 1

        End If

        If lngInstanceCount = lngInstance Then Exit Function

    Next intLenLoop

    lngStartPosition = 0 ' CVErr(xlErrValue)

End Function


Sub TestIt()

    Dim strText As String

    Dim strFind As String

    strText = "Five circles circling a centre circle. How many circles are there in all ?"

    strFind = "Circle"

    MsgBox lngStartPosition(strText, strFind, 2)

End Sub