You are currently offline, waiting for your internet to reconnect

How To Search for the Last Occurrence of a Substring

This article was previously published under Q168836
SUMMARY
The InStr() function provides a method to search for the first occurrenceof one string (substring) inside another string. However, there is nointrinsic method to search for the last occurrence of a substring. Thisarticle provides a sample function written in Basic.
MORE INFORMATION
There are two main methods of searching for the last occurrence of asubstring:

  1. Searching from the right-hand end of the string and stopping when the first match is found.
  2. Searching from the left-hand end of the string until no more matches are found, and remembering the location of the last match.
While the first method would seem the logical approach (and would be in alanguage such a C that allowed direct pointer manipulation), the secondmethod is nearly always faster in Visual Basic using the InStr() functionto search forward through the string.

This is because InStr() is highly optimized and many orders of magnitudefaster than emulating the functionality via looping through the stringusing the Mid$() function to extract each character.

The code example below demonstrates the second method.

WARNING: Microsoft provides code/macro examples for illustration only,without warranty either expressed or implied, including but not limited tothe implied warranties of merchantability and/or fitness for a particularpurpose. This code is provided 'as is' and Microsoft does not guaranteethat the following code can be used in all situations. Microsoft does notsupport modifications of the code to suit customer requirements for aparticular purpose.

NOTE: In versions of Basic that don't support the "_" line continuationcharacter, the split lines must be entered on a single line when typing thecode into the module.

Step-by-Step Example

  1. Create a new project and add a module:
          Function InStrR(ByVal sTarget As String, _                      ByVal sFind As String, _                      ByVal iCompare As Long) As Long      Dim P As Long, LastP As Long, Start As Long        P = InStr(1, sTarget, sFind, iCompare)        Do While P          LastP = P          P = InStr(LastP + 1, sTarget, sFind, iCompare)        Loop        InStrR = LastP      End Function						
  2. In Visual Basic only, run the project and when the default Form1 is displayed, pause it.
  3. To test this function, type the following line in the Debug/Immediate window, and then press the ENTER key:
    ?InStrR("The quick brown fox jumped over the lazy dog", "the", 0)
    You should see 33 as the result.

    Values for iCompare can be:
          0   Binary comparison      1   Text comparison      2   Database Comparison (Microsoft Access)
REFERENCES
Microsoft Visual Basic online Help topic "InStr."
Properties

Article ID: 168836 - Last Review: 10/11/2006 18:40:47 - Revision: 3.7

  • Microsoft Visual Basic 5.0 Control Creation Edition
  • Microsoft Visual Basic 5.0 Learning Edition
  • Microsoft Visual Basic 5.0 Professional Edition
  • Microsoft Visual Basic 5.0 Enterprise Edition
  • Microsoft Visual Basic 4.0 Standard Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 Professional Edition
  • Microsoft Visual Basic 4.0 16-bit Enterprise Edition
  • Microsoft Visual Basic 4.0 32-Bit Enterprise Edition
  • Microsoft PowerPoint 97 Standard Edition
  • Microsoft Word 97 Standard Edition
  • Microsoft Excel 95 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft Access 97 Standard Edition
  • Microsoft Access 95 Standard Edition
  • kbhowto kbprogramming KB168836
Feedback