How to Use VBA InstRev 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 InstRev function might be the best option for you. Unlike the InStr function, the InstRev function starts searching from the end of the string. In this article, I’ll discuss the ins and outs of the InstRev function including 7 examples also with the necessary explanation.

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

Overview of VBA InstRev 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 InstRev Function in Excel

InstRev, 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.

Formula of VBA InstRev

  • Syntax:

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

  • Arguments:

The InstrRev function has four arguments. These are the following-

Argument Required/Optional Explanation
StringCheck Required The string from where you want to get the position
StringMatch Optional The substring that you wish to get
Start Optional Refers to starting position. The default value of this argument is -1 (starting from right to left); if you omit the value.
Compare Optional Represents three kinds of comparisons as shown in the following table. Excel takes vbBinaryCompare (value:0) if you skip the value.

Three types of comparisons of the Compare argument are-

VBA Constant Value Description
vbUseCompareOption -1 Uses option compare
vbBinaryCompare 0 Binary comparison
vbTextCompare 1 Textual comparison
  • Return Value:

Returns a numeric value.


Examples of Using VBA InstRev 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 InstRev 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 InstRev 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.

How to Insert VBA Code

Secondly, go to Insert>Module.

How to Insert VBA Code

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

VBA InstRev to Find out The Position of A Substring

Step 3:

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

Then, you’ll see the output is 1.

VBA InstRev to Find out The Position of A Substring

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 InstRev 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

Get The Position of A Substring with Given Starting Position

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

Get The Position of A Substring with Given Starting Position

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


3. VBA InstRev to Extract the Position in Case Sensitivity

At the current example, we’ll examine whether the InstRev 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

VBA InstRev to Extract The Position in Case Sensitivity

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

Extract The Position in Case Sensitivity

As the output is 0, it is clear that the InstRev 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

Extract The Position in Case Sensitivity

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.

VBA InstRev to Extract The Position in Case Sensitivity

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

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


Similar Readings:


4. VBA InstRev 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

Retrieve the Position of Last Space

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

Retrieve the Position of Last Space

Related Content: How to Use VBA Replace Function in Excel (11 Applications)


5. VBA InstRev 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 InstRev function.

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

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

And the second time, the InstRev 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

VBA InstRev to Get The Position of Second to Last Space

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

VBA InstRev to Get The Position of Second to Last Space

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


6. VBA InstRev 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

VBA InstRev to Find out The Position of “ZZ” within The String

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

VBA InstRev to Find out The Position of “ZZ” within The String

Read More: How to Use VBA StrComp in Excel (5 Common Examples)


7. VBA InstRev 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 InstRev 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.

Retrieve the Position of Last Space in Case of A Dataset

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

Retrieve the Position of Last Space in Case of A Dataset

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

Retrieve the Position of Last Space in Case of A Dataset

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


Things to Remember

1. The InstRev 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 InstRev and InStr functions are not the same in VBA.


Conclusion

In short, I discussed the basics of the VBA InstRev 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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo