VBA InStr and InStrRev – How to Use

As the name suggests VBA InStr function looks for a substring embedded inside another string and then returns its starting position.
 
If it cannot find the string inside the parent string then it returns 0. It is a very important function among the string functions in VBA. It is probably the best function to perform string search operations in VBA.
 
In this post I will explain InStr and InStrRev functions in VBA with examples:

VBA Instr Function

Syntax of VBA Instr function:

The syntax of Instr is as follows:

Instr( [start], parent_string, substring, [compare] )

Here, ‘start’ refers to the position from where the search should begin. It is an optional parameter and if it is omitted then string search begins from 1st position inside the ‘parent_string’

‘parent_string’ is the string inside which the ‘substring’ is to be searched.

‘substring’ is the string which is to be searched inside the ‘parent_string’

‘compare’ specifies the comparison algorithm by which the search is to be made. It is also an optional parameter. In advanced cases compare parameter can have any one of the following values:

Parameter Value

Description

vbBinaryCompare Specifies that comparison should be Binary comparison i.e. each character should only match itself. This is the default value.
vbTextCompare Specifies that comparison should be Text to Text comparison.
vbDatabaseCompare Specifies to use the locale settings of a Database for comparison rather than straight text.

 

Few Important points about Instr function:

  • If anyone of the strings i.e. ‘parent_string’ or ‘substring’ is Null. Then VBA Instr statement returns a Null and hence can cause a code to break. So, before passing any value to Instr Function check if the value is valid or not. Also, make sure that you have done proper Error Handling in the code.
  • It returns the ‘start’ parameter, if the ‘substring’ parameter is empty.
  • It returns a 0 if ‘substring’ is not present inside the ‘parent_string’.
  • If ‘start’ parameter’s value is greater than the length of ‘parent_string’ then also Instr function returns a 0.

Examples of Instr Function:

Now, let’s take some examples of VBA Instr function:

1. InStr("Excel-Trick", "Trick") will return a result 7 as the string “Trick” starts at the seventh position inside the ‘parent_string’ i.e. “Excel-Trick”.

2. InStr("Excel-Trick", "c") will result 3 as the character “c” first comes at the third position inside the ‘parent_string’.

3. InStr(4, "Excel-Trick", "c") will result into 10 as here we are starting the search for “c” from the 4th character and hence Instr gives us the position of second “c” (Excel-Trick) in the ‘parent_string’.

4. InStr("Alligator", "z") will result into 0 as there is no “z” character inside the parent string.

5. InStr(1, "Alligator", "a") will result into 6 as the lowercase “a” character is present at position 6 inside the word “Alligator”.

6. InStr(1, "Alligator", "a", vbTextCompare) will result into 1 as here we have used ‘vbTextCompare’ which ignores the difference between upper case and lowercase characters.

Using vbTextCompare is not the best way:

vbTextCompare is not a safe option to use while you are dealing with case-insensitive strings. The reason for this is because many times it can lead into misleading results while handling some characters:

For example:

InStr(1,"Ætna","et",vbTextCompare) results into 1, because the character “Æ” is a Latin character which stands for “et”. And hence vbTextCompare treats “Æ” equal to “et”, this behaviour of vbTextCompare is quite misleading.

So, in the cases where you are dealing with case-insensitive characters like in Example 6, a better way would be:

InStr( LCase("Alligator"), LCase ("a"))

Here LCase function converts both the strings to lower case characters and the InStr statement results into 1.

Reverse String Search Function in VBA: InStrRev

InStrRev function is similar to the VBA InStr, with only difference being that it starts searching the ‘substring’ inside the ‘parent_string’ from end to start.

The syntax of InStrRev is as follows:

InStrRev (parent_string, substring, [start], [compare])

Here also the ‘parent_string’ refers to the string within which the ‘substring’ is to be searched.

‘substring’ is the string that is to be searched inside the ‘parent_string’.

‘start’ is again an optional parameter that decides from which position the search is to be started.

‘compare’ parameter refers to the type of comparison like: vbBinaryCompare, vbTextCompare and

vbDatabaseCompare.

Note: Note the difference in the place of parameters of both InStr and InStrRev functions.

Example of InStrRev function:

InStrRev("aligator", "a") results into 5. InStrRev function starts searching for the “a” character inside the ‘parent_string’ from backwards and when it encounters the first “a” (from backwards) it checks its position from the forward direction and gives it position as the result.

The practical use of InStrRev can be in finding the last index of a character inside a string.

Sample Macro having both VBA InStr and VBA InStrRev:

Below is a self-explanatory macro in which I have used both InStr and InStrRev functions:

Sample Macro having both VBA InStr and VBA InStrRev

Sub SearchStrings()
Start_pos = InStr("aesthesia", "a")
Last_pos = InStrRev("aesthesia", "a")
MsgBox "InStr Output =" & Start_pos & vbNewLine & "InStrRev Output =" & Last_pos
End Sub

So, this was all about InStr and InStrRev functions in VBA. Do let us know if you have any doubts or queries related to the topic.

About Ankit Kaul

Ankit is the founder of Excel Trick. He is tech Geek who loves to sit in front of his square headed girlfriend (his PC) all day long. :D. Ankit has a strong passion for learning Microsoft Excel. His only aim is to turn you guys into 'Excel Geeks'.

Privacy Policy | Disclaimer | About