How to search for 2nd occurrence of a string in another string in VBA ?
For example , in below given string,the word "test" occurs twice.
this is a test to test
5 Answers
Simply do the search twice
Sub Demo() Dim DataString As String Dim SearchString As String Dim i As Long DataString = "this is a test to test" SearchString = "test" i = InStr(1, DataString, SearchString, vbTextCompare) i = InStr(i + 1, DataString, SearchString, vbTextCompare) Debug.Print "Second occurance starts at position " & i End Sub update
To just find the last occurrence then just
MsgBox InStrRev("this is a test to test", "test") initial answer which handled less than 2 occurrences
A little ungainly but handles 1 or 0 occurences
Dim strIn As String Dim strOut As String Dim lngPos As Long Dim lngPos2 As Long strIn = "test" strOut = "this is a test to test" lngPos = InStr(strOut, strIn) If lngPos > 0 Then lngPos2 = InStr(lngPos + 1, strOut, strIn) If lngPos2 > 0 Then MsgBox strIn & " at " & lngPos2 Else MsgBox "No " & strIn End If To find the 2nd occurrence of a string, use the InStr function twice. Note that the substring for which you search could be a repeating character sequence like "dd". In this case you have to determine if you want to return 5 or 6 as the result of searching for "bb" in "abcdddd". That is, do you want to start searching for the second occurrence of "dd" at at the end of the first occurrence or at the second character of the first occurrence?
Private Sub ExampleFind2ndOccurrence() Dim intFirst As Integer, intSecond As Integer Dim searchThisString As String: searchThisString = "abcdddddefg" Dim forThisSubString As String: forThisSubString = "dd" ' Find the first occurrence of forThisSubString intFirst = InStr(1, searchThisString, forThisSubString, vbTextCompare) ' Find the second occurrence of forThisSubString intSecond = InStr(1, Mid(searchThisString, intFirst + 1), forThisSubString, vbTextCompare) If intSecond > 0 Then intSecond = intFirst + intSecond Debug.Print "2nd occurrence occurs at character position "; intSecond ' Alternate method to find second occurrence of forThisSubString in the ' case where there can be no overlap intSecond = InStr(1, Mid(searchThisString, intFirst + Len(forThisSubString)), forThisSubString, vbTextCompare) If intSecond > 0 Then intSecond = intFirst + Len(forThisSubString) - 1 + intSecond Debug.Print "Prohibbitting overlap, 2nd occurrence occurs at character position "; intSecond End Sub Find the Nth occurrence of a substring:
Public Function InStr2(ByVal IntStartPosition As Variant _ , ByVal Str As String _ , ByVal SubStr As String _ , Optional IntCompareMethod As Integer = vbTextCompare _ , Optional IntOccurrence As Integer = 1 _ , Optional BlnOverlapOK As Boolean = False) ' Find the IntOccurrence instance of SubStr in Str ' Parameters: ' IntStartPosition (Integer): the character position at which to start searching. ' (See docs for InStr) ' Str (String): the string to search. (See docs for InStr) ' SubStr (String): the substring to find in Str. (See docs for InStr) ' IntCompareMethod (integer): a VBA compare enumeration value. (See docs for InStr) ' IntOccurrence (integer): The number of instances of SubStr for which to search ' BlnOverlapOK (boolean): Is it okay for the Nth occurence of SubStr to overlap the ' N-1 occurrence? ' Returns the location of the occurence of the IntOccurrence instance of SubStr in Str Dim s As String Dim intCharPos As Integer Dim cnt As Integer Dim intStart As Integer Dim i As Integer ' Initialize If IsMissing(IntStartPosition) Then IntStartPosition = 1 intStart = IntStartPosition Str = Mid(Str, intStart) intCharPos = 1 cnt = 0 i = 1 Do While intCharPos <= Len(Str) And cnt < IntOccurrence s = Mid(Str, intCharPos) i = InStr(1, s, SubStr, IntCompareMethod) If i = 0 Or i = Null Then InStr2 = i Exit Function End If cnt = cnt + 1 If BlnOverlapOK Or Len(SubStr) = 1 Or cnt = IntOccurrence Then intCharPos = intCharPos + i Else intCharPos = intCharPos + i + Len(SubStr) - 1 End If Loop InStr2 = intCharPos - 1 End Function Example of finding nth occurrence of substring:
Private Sub InStr2Example() Dim i As Integer Dim searchThisString As String: searchThisString = "abcddddddd" '1234567890 Dim forThisSubString As String: forThisSubString = "dd" i = InStr2(1, searchThisString, forThisSubString, vbTextCompare, 3, True) Debug.Print "3rd occurrence occurs at character position "; i i = InStr2(1, searchThisString, forThisSubString, vbTextCompare, 3, False) Debug.Print "Prohibbitting overlap, 3rd occurrence occurs at character position "; i End Sub You will need to find where the first occurrence starts and then offset the search range accordingly.
A nested Mid/InStr function will do the trick:
Dim x As String, fVal As String x = "test this is a test" fVal = "test" y = Mid$(Mid$(x, InStr(x, fVal) + Len(fVal)), InStr(Mid$(x, InStr(x, fVal) + Len(fVal)), fVal)) Debug.Print y A flexible function to find any occurrence using Split() could be:
Function GetPosition(ByVal FullText As String, ByVal SearchString As String, ByVal occurrence As Long, Optional ByVal CaseSensitive As Boolean = False) As Long 'Purpose: get start position of a given search occurrence within fulltext '[0]case sensitive? (case insensitive by default) If Not CaseSensitive Then FullText = LCase(FullText): SearchString = LCase(SearchString) End If '[1]split fulltext into substrings Dim part: part = Split(FullText, SearchString) ' split fulltext If occurrence < 1 Then Exit Function ' accept only positive occurrencies If occurrence > UBound(part) Then Exit Function ' refuse too high occurrencies '[2]add substrings plus searchstring lengths Dim i As Long, n As Long ' counters For i = 0 To occurrence - 1 n = n + Len(part(i)) ' add part lengths Next n = n + (occurrence - 1) * Len(SearchString) + 1 '[3]return search position of wanted occurrence GetPosition = n End Function Example call
Sub Test() Dim s As String: s = "this is a test to test to test" ' (three occurrencies of "test") Dim i As Long For i = 1 To 4 Debug.Print "Occurrence " & i, "starts at position " & GetPosition(s, "tEst", i) Next End Sub 
