How to Use VBA InStrRev Function (7 Suitable Examples)

When you need to find the position of a character inside a string or the occurrence of a substring inside a string, the VBA InStrRev function might be the best option. Unlike the InStr function, the InStrRev function starts searching from the end of the string. In this article, we will illustrate the ins and outs of the InStrRev function using 7 examples.

The following is an overview of the InStrRev function.

Download Practice Workbook

Introduction to the VBA InStrRev Function in Excel

InStrRev, which refers to “In String Reverse”, is a VBA function to get the position of a substring within a given string.

• Function Objective:

Finds out the position of a string that occurs within another, starting from the beginning.

• Syntax:

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

• Arguments:

The InStrRev function has four arguments:

Three types of comparisons of the Compare argument are-

• Return Value:

Returns a numeric value.

Examples of Using VBA InStrRev Function in Excel

In the first 6 examples, you’ll see the uses of the InStrRev function for a single string i.e. “Miles to Go Before I Sleep”.

Example 1 – VBA InStrRev to Find out the Position of a Substring 

We’ll use the function to find out the position of a substring “M” inside the string “Miles to Go Before I Sleep”.

Step 1: 

Open a module by clicking Developer > Visual Basic.

Go to Insert>Module.

Step 2: 

Enter the following code in your module.

Sub Finding_The_Position_Substring()
Dim A As Integer
A = InStrRev("Miles to Go Before I Sleep", "M")
MsgBox A
End Sub

Step 3:

Run the code (F5 is the keyboard shortcut).

You’ll see the output is 1.

Note: Sometimes the StringCheck is called simply a “string” and the StringMatch is also called a “substring”.

Read More: How to Call a Sub in VBA in Excel (4 Examples)

Example 2 – VBA InStrRev to Get the Position of a Substring with Given Starting Position 

In the previous example, we skipped the value of the Start argument. What if we put 10 as a starting position for the substring “e”?.

Enter the following code into the module.

Sub Getting_Position_Substring_Given_Starting_Position()
Dim A As Integer
A = InStrRev("Miles to Go Before I Sleep", "e", 10)
MsgBox A
End Sub

Run the code, and you’ll get the output 4.

Related Content: How to Use Fix Function in Excel VBA (4 Examples)

Example 3 – VBA InStrRev to Extract the Position in Case Sensitivity

We’ll now examine whether the InStrRev function is case sensitive or not.

That means, if we put the substring as “g” and run the code, what will be the output.

After running the code, if we get 0, it means that the function is not case sensitive.

Enter the following code into a new module.

Sub Extracting_The_Position_Case_Sensitivity()
Dim A As Integer
A = InStrRev("Miles to Go Before I Sleep", "g")
MsgBox A
End Sub

Run the code, and the output will look as follows.

As the output is 0, it is clear that the InStrRev function is not case-sensitive.

In the above code, we skipped the value of the Compare argument, so Excel takes vbBinaryCompare as the default value.

If you input the argument as vbTextCompare, you’ll see a different output for the same string and substring.

The adjusted code will be as follows.

Sub Extracting_The_Position_Case_Sensitivity()
Dim A As Integer
A = InStrRev("Miles to Go Before I Sleep", "g", , vbTextCompare)
MsgBox A
End Sub

After running the code, you’ll get the output 10 (not 0), which means the function is case sensitive in this case.

The case sensitivity of the InStrRev function depends on using  the Compare argument.

Read More: How to Use InStr Function in VBA (3 Examples)

Example 4 – VBA InStrRev to Retrieve the Position of Last Space

Enter the following code in a new module.

Sub Retrieve_Position_Last_Space()
Dim A As Integer
A = InStrRev("Miles to Go Before I Sleep", " ")
MsgBox A
End Sub

Run the code, and you’ll get the following output.

Related Content: How to Use VBA Replace Function in Excel 

Example 5 – VBA InStrRev to Get the Position of Second to Last Space

To find the position of second to the last space using the InStrRev function, you have to utilize the function two times.

The first instance of the InStrRev function returns the position of the last space and the InStrRev function returns the position of second to the last space.

Enter the following code into a new module.

Sub Getting_Position_Second_to_Last_Space()
Dim LastPos As Integer
Dim SecondLastPos As Integer

LastPos = InStrRev("Miles to Go Before I Sleep", " ")
SecondLastPos = InStrRev("Miles to Go Before I Sleep", " ", LastPos - 1)

MsgBox SecondLastPos
End Sub

Run the code and the output will look as follows.

Read More: [Fixed!] Excel VBA InStrRev Not Working

Example 6 – VBA InStrRev to Find out the Position of “ZZ” within the String

In this example, we’ll see the process of finding out the position of “ZZ” within the string. “ZZ” is a substring (StringMatch) that is not available in the string.

Enter the following code into a new module.

Sub Finding_The_Position_ZZ()
Dim A As Integer
A = InStrRev("Miles to Go Before I Sleep", "ZZ")
MsgBox A
End Sub

Run the code and the output is 0 as shown in the following image.

Read More: How to Use VBA StrComp in Excel 

Example 7 – VBA InStrRev to Retrieve the Position of Last Space in Case of a Dataset

In most cases, we use a dataset in Excel and want to find the output for the dataset.

Assuming that in our dataset we have four strings (starting from B5 cell) and we want to find the position of the last space for each string.

Enter the following code into a new module.

Sub Dataset_Example_InStrRev()
Range("D5").Value = InStrRev(Range("B5"), " ")
Range("D6").Value = InStrRev(Range("B6"), " ")
Range("D7").Value = InStrRev(Range("B7"), " ")
Range("D8").Value = InStrRev(Range("B8"), " ")
End Sub

Run the code and the output will be as follows.

Things to Remember

1. The InStrRev function finds 0 if the string match or substring is not found.

2. It returns Null if the string check or string match is Null.

3. If the value of the Start argument is larger than the length of the string check, the function returns 0.

4. The InStrRev and InStr functions are not the same in VBA.

Related Articles

• Excel VBA ASC() Function – Get ASCII Value of Character
• How to Use VBA Space Function in Excel 
• How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA
• Use TRIM Function in VBA in Excel (Definition + VBA Code)
• How to Use VBA SPLIT Function in Excel (5 Examples)
• How to Use MsgBox Function in Excel VBA (A Complete Guideline)