Instr() Function

This is a VBA function and not a Worksheet function (Excel formula). This function returns the Position of First Occurrence of a SubString in a String. SubString could be a letter, Word or Sentence etc. It means this function always returns an Integer value. As mentioned in the definition of this function, it always returns the position number of the very First occurrence. Once first occurrence found, search stops there and returns the position number. It does not go and check any other occurrence.

Syntax:

This is how Syntax will look like as soon as you type VBA.Instr in VB Editor

VBA.Instr(<Start>, <String1>, <String2>, <CompareMethod>)

Parameters:

Start(Optional) :

This is the start position where you want to start the search of your sub-string to find its first occurrence in the main string. This is an optional argument. If omitted search starts from the first character of the main string.

String1(Mandatory) :

This is the main string in which the sub-string is to be searched. This is a mandatory argument.

String2(Mandatory) :

This is the sub-string which has to be searched in the main string to find its first occurrence. This is again a mandatory argument.

CompareMethod (Optional) :

This is a Numeric value ( 0 or 1) to indicate which method to use for comparison. 0 is for performing a binary comparison. 1 is for performing a textual comparison. The default method is 0 – vbBinaryCompare

Examples:

With the above theory you may not be more comfortable until you practice with the some example by running the VBA code. I have taken few possible cases with different parameters in the below examples. They will make you more comfortable in using this function 🙂

Example 1 : Omitting all the Optional Parameters

In the below Example, I have omitted all the optional parameter and passed the String1 and String2 which are the mandatory parameters in this function.

String1 : My Name is Vishwa Mitra vishwa mishra
String2 : vishwa

 
VBA.Instr function will look like below with the above 2 argument passed in it:
 
VBA.Instr(“My Name is Vishwa Mitra vishwa mishra”, “vishwa”)
Following picture shows the integer value which is returned by the above VBA statement (refer the below picture)
 

Explanation:

In the above code all the optional parameters are omitted. Hence using all the default values for other parameters, this function has returned the first occurrence of sub-string vishwa in the main string. This function has returned the count as 12.
In the above example default Start number is passed as 1 and compare method is passed as 0 – BinaryMethod.

Example 2 : What is difference between Binary and Text comparison?

It is very simple. As the name suggests in binary comparison, system compares the Numeric unicode value of each character. But in Text Comparison, comparison is based on the reference of the current world definition.
For example: Upper case – V and Lower case – v. Both “V” and “v” has different unicode value but as a text both are same. This is the reason Below statement 1 returns the first occurrence as 1 while the statement 2 returns as 8 while both the statements are exactly same except the comparison method. Refer the below two examples:

Statement 1: Text Comparison

SubStringFoundAt = VBA.InStr(1, “Vishwa vishwa Mitra mishra”, “vishwa”, 1)

Statement 2: Binary Comparison

SubStringFoundAt = VBA.InStr(1, “Vishwa vishwa Mitra mishra”, “vishwa”, 0)

With the above example difference between Binary Comparison and Text Comparison is clear. Though, rest all the parameters are same in both the statement but still result is different because comparison method is different. Reason is that in Binary Comparison Vishwa is not equal to vishwa but in text comparison they are equal.

Example 3 : Impact of Start number

In the below example I will show you how Start number has an impact on the integer returned by this function.

Function INSTR_EXAMPLE()
Dim myString As String
Dim SubStringFoundAt1 As Integer
Dim SubStringFoundAt2 As Integer
    SubStringFoundAt1 = VBA.InStr(1, "vishwa vishwa Mitra mishra", "vishwa", 0)
    SubStringFoundAt2 = VBA.InStr(2, "vishwa vishwa Mitra mishra", "vishwa", 0)
End Function

In the above code first statement returns the integer as 1 while the second statement returns as 8> as shown in the below picture:

Explanation:

Case 1 : In the first statement the Start number is set as 1 hence Instr() function starts the search of sub-string vishwa right from the first letter of the main string vishwa vishwa Mitra mishra and it finds the first occurrence at the first place itself hence it returned the integer as 1
Case 2 : In the second statement start number is passed as 2; it means the Instr() VBA function will start the search from the second letter of the string vishwa vishwa Mitra mishra. This is the reason in this case this function will find the first occurrence of sub-string vishwa at the 8th position and it returned the integer as 8

Special Cases in INSTR()Function

Following are special cases in this function:

Case 1 : Sub-String NOT Found

Zero (0) is returned by Instr(), if the Sub-String is not found in the main string from the start number for search provided in the parameter. In the given example though the string vishwa is found but not starting from the 5th letter (Start=5) hence this function will consider as Sub-String NOT found in the main string and will return Zero (0) as shown in the below picture.

Case 2 : Main String passed with Zero length

Zero (0) is returned by Instr() function, if main string is passed of Zero length. Refer the below picture

Case 3 : Sub-String passed with Zero length

In this case this function returns the Start Number passed. Refer the below picture:

Case 4 : Start > length of Main string AND Sub-String is NOT of Zero Length

In this case when Start number is greater than the length of the main string AND sub-string is NOT of Zero length, Zero is returned. Refer the below picture.

Function INSTR_EXAMPLE()
Dim myString As String
Dim Case1 As Integer
Dim Case2 As Integer
Dim Case3 As Integer
    Case1 = VBA.InStr(5, "vishwa Mitra mishra", "vishwa", 0)
    Case2 = VBA.InStr(1, "", "vishwa", 0)
    Case3 = VBA.InStr(10, "vishwa Mitra mishra", "", 0)
    Case4 = VBA.InStr(100, "vishwa Mitra mishra", "vishwa", 0)
End Function

Explanation:

Case 1. It returns Zero because vishwa is NOT found in the string 5th letter (because Start = 5)
Case 2. Returns Zero because the length of the main string is Zero.
Case 3. In this case it returns the Start number passed in the function because the sub-string to be searched is of zero length.
Case 4. This returns Zero because the Start (100) is greater than the length of the main string (19) AND String2 is not of Zero length.

Have you got any question or doubt related to INSTR() Function?

Do comment of mail me. I try my best to clarify.