VBA INSTRREV Function (Syntax + Example)


- Written by Puneet

The VBA INSTRREV function is listed under the text category of VBA functions. When you use it in a VBA code, it returns the starting position number of a sub-string (specified by you) from another string. Unlike INSTR, it starts searching for the sub-string from the right side of the string, which means the end to start.


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


  • StringCheck: The string in which you want to search.
  • StringMatch: The string that you want to search for.
  • [Start]: An integer to specify the position from where you want to start the search [This is an optional argument and if omitted VBA takes 1 by default].
  • [Compare]: A string value to define the comparison to make while filtering the array [This is an optional argument and if omitted VBA takes vbBinaryCompare by default].
    • vbBinaryCompare: For binary comparison.
    • vbTextCompare: For text comparison.
    • vbDatabaseCompare: For Database Comparison. 


To practically understand how to use the VBA INSTRREV function, you need to go through the below example where we have written a vba code by using it:

Sub example_INSTRREV()
Range("B1").Value = InStrRev(Range("A1"), " ")
End Sub

In the above code, we have used the INSTRREV to find the space within the value (Excel Champs) in cell A1 and it has returned 6 as a result, there’s the first space is between “Excel” and “Champs” whose position is 6.


  • If “stringcheck” or “stringmatch” is NULL then it will return NULL in the result.
  • If “stringmatch” cannot be found in “stringcheck” then will 0 is the result.
Last Updated: June 22, 2023