VBA InStrRev Function

Related Function:
VBA InStr

Description

The Excel VBA InStrRev function returns an integer representing the position of a substring within a string. The Function searches the string from right to left (i.e. from the end to the start of the string).

If the substring is not found, the function returns the value 0.

The syntax of the InStrRev function is:

InStrRev( StringCheck, StringMatch, [Start], [Compare] )

Where the function arguments are:

StringCheck-The string that you want to search.
StringMatch-The substring that you want to search for.
[Start]-

An optional integer argument, representing the position (from the start of StringCheck) that you want to start searching from.

If omitted, the [Start] argument takes on the default value of -1, meaning that the search starts from the end of the string.
[Compare]-

An optional argument representing the type of comparison to make.

This can be any of the following values:
vbBinaryCompare-performs a binary comparison
vbTextCompare-performs a text comparison
vbDatabaseCompare-performs a database comparison
  If omitted, the [Compare] argument takes on the default value vbBinaryCompare.


VBA InStrRev Function Examples

Example 1

' Example 1. Find the position of the last space (i.e. the substring " ") within the string
' "John Michael Paul Smith".
Dim pos As Integer
pos = InStrRev( "John Michael Paul Smith", " " )
' This returns the result 18.

In the example above:


Example 2

' Example 2. Find the position of the last space that occurs within the string "John Michael
' Paul Smith" before character 17.
Dim pos As Integer
pos = InStrRev( "John Michael Paul Smith", " ", 17 )
' This returns the result 13.

In the example above, the search begins at position 17 of the string "John Michael Paul Smith" and so the function returns the space between the middle names "Michael" and "Paul". I.e. the space at position 13.


Example 3

' Example 3. Find the position of the last and second to the last spaces within the string
' "John Michael Paul Smith".
Dim lastSpacePos As Integer
Dim secondLastSpacePos As Integer
lastSpacePos = InStrRev( "John Michael Paul Smith", " " )
secondLastSpacePos = InStrRev( "John Michael Paul Smith", " ", lastSpacePos - 1 )
' The variables lastSpacePos and secondLastSpacePos are now equal to 18 and 13 respectively.

In the example above:


Example 4

' Example 4. Find the last occurrence of the text "ZZ" within the string "John Michael Paul Smith".
Dim pos As Integer
pos = InStrRev( "John Michael Paul Smith", "ZZ" )
' The text "ZZ" is not found and so the function returns the value 0.

In the above example, the sub-string "ZZ" is not found and so the InStrRev function returns the value 0.


Further details and examples of the VBA InStrRev function are provided on the Microsoft Developer Network.