VBA InStrRev Function

Related Function:

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:

The [Start] argument has not been provided to the function and so it is set to the default value -1 (i.e. start searching from the end of the string);
The [Compare] argument has also not been provided to the function and so it is set to the default value vbBinaryCompare;
The result of the InStrRev function (the integer 18) is assigned to the variable pos.

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:

The InStrRev function finds the position of the last space and stores this (the returned value 18) in the variable lastSpacePos.
The second call to InStrRev starts at position lastSpacePos - 1 (i.e. at position 17). This call therefore returns the position of the second to the last space. I.e. position 13.

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.

Return to the List of VBA Functions
Return to the ExcelFunctions.net Home Page