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 for you. Unlike the InStr function, the InStrRev function starts searching from the end of the string. In this article, I’ll discuss the ins and outs of the InStrRev function including 7 examples also with the necessary explanation.

Firstly, let’s have a look at the overview of the InStrRev function.

Right away,  I’ll show you the uses of the function elaborately. Before that, let’s be introduced to the 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. If you enter the function in the module, you’ll see such as the following screenshot.

• 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. These are the following-

Three types of comparisons of the Compare argument are-

• Return Value:

Returns a numeric value.

Examples of Using VBA InStrRev Function in Excel

After finishing the introduction part, you have concrete knowledge about the function. So, we may accelerate the knowledge with proper examples. 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”. In the last example, I’ll discuss the use of the function in the case of a dataset or worksheet if you need it.

Let’s dive into the examples.

1. VBA InStrRev to Find out the Position of a Substring 

In the beginning, we’ll see the uses of the function to find out the position of a substring “M” inside the string “Miles to Go Before I Sleep” in step by step process.

Step 1: 

Firstly, open a module by clicking Developer > Visual Basic.

Secondly, go to Insert>Module.

Step 2: 

Then, copy 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:

Finally, run the code (F5 is the keyboard shortcut).

Then, 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)

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

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

For doing this, you have to insert a module as shown earlier and copy 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

If you run the code, you’ll get the output is 4.

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

3. VBA InStrRev to Extract the Position in Case Sensitivity

At the current example, we’ll 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 representing that the function is not case sensitive.

Copy 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

If you run the code, the output will look as follows.

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

But we left another important issue.

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

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

So the adjusted code will be the following.

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 is 10 (not 0), that is to say, the function is case sensitive in this case.

To sum up, we can say that the case sensitivity of the InStrRev function depends on using  the Compare argument

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

4. VBA InStrRev to Retrieve the Position of Last Space

Again, you may retrieve the position of the last space (“ “) from a given string.

Copy the following code and insert that 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

If you run the code, you’ll find the following output.

Related Content: How to Use VBA Replace Function in Excel 

5. VBA InStrRev to Get the Position of Second to Last Space

More importantly, if you wish to find the position of second to the last space, you may use the InStrRev function.

In such a situation, you have to utilize the function two times.

For the first time, the InStrRev function returns the position of the last space.

And the second time, the InStrRev function returns the position of second to the last space.

Copy 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

Next, run the code and the output will look as follows.

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

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

In the last example of a single string, we’ll see the process of finding out the position of “ZZ” within the string.

Here the “ZZ” means such a substring (StringMatch) that is not available in the string.

So copy the code and insert it 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

Finally, run the code and the output is 0 as shown in the following figure.

Read More: How to Use VBA StrComp in Excel 

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

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

For your convenience, I’ll show the use of the InStrRev function in the case of a 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.

Next, copy the following code after creating 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

Subsequently, run the code and the output will be as follows.

Related Content: How to Use LCase Function in VBA in Excel 

Things to Remember

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

2. The 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.

Conclusion

In short, I discussed the basics of the VBA InStrRev function at first. Then 7 distinct examples are also shown for accelerating the knowledge. Anyway, if you have any queries and suggestions, please don’t forget to share them in the following comments section.

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)