Excel VBA InStrRev Function
VBA InStrRev Function: The InStrRev function in Excel VBA returns an integer representing the position of a substring within the specified string if the substring is fount else it returns the value 0. It searches the string from right to left (i.e. from the end to the start of the string).
Syntax
InStrRev( StringCheck, StringMatch, [Start], [Compare] )
Parameter
StringCheck (required) – This parameter represents the string you want to search.
StringMatch (required)- This parameter represents the substring you want to search for.
Start (optional)- This parameter represents an integer argument, representing the position that you want to start the searching. The default value is -1 (search starts from the end of the string).
Compare (optional) - This parameter represents the type of comparison to make.
It can take any of the following values:
- vbBinaryCompare: To perform a binary comparison
- vbTextCompare: To perform a text comparison
- vbDatabaseCompare: To perform a database comparison
Return
This function returns an integer representing the position of a substring within the specified string if the substring is fount else it returns the value 0.
Example 1
Sub InStrRev_Example1()
Dim pos As Integer
'returns an integer representing the position of a substring within a string
rev = InStrRev("VBA is an easy programming language", "easy", 17)
'This returns the result 10.
ActiveCell.Value = rev
End Sub
Output
10
Example 2
Sub InStrRev_Example2()
'Finding the position of the last space that occurs within the string before the character 17.
Dim rev As Integer
rev = InStrRev("VBA is an easy programming language", " ", 17)
' This returns the result 15.
ActiveCell.Value = rev
End Sub
Output
15
Example 3
Sub InStrRev_Example3()
'Find the last occurrence of the "ZZ" within the string.
Dim rev As Integer
rev = InStrRev("VBA is an easy programming language", "ZZ")
'The text "ZZ" is not found and so the function returns the value 0.
ActiveCell.Value = rev
End Sub
Output
0
