VBA InStr

The VBA InStr function helps find the position of a given substring within a string. It returns the first occurrence of the substring in the form of an integer (output). A string is a series of characters or text supplied to the function in double quotation marks.

For example, the InStr can extract a substring from a sentence, apply the desired font to a particular string, find the position of a character within the string, and so on.

The VBA InStr function in excel begins searching from left to right.

The syntax of the function is shown in the following image:

The function accepts the following arguments:

• Start: This is the position from which the function begins to search. For example, if “start” is set at 3 and the character “a” is to be found in the word “Bangalore,” the output is 5.
• String 1: This is the actual string within which the substring is to be found. For example, if the character “a” is to be found in the word “Bangalore,” “string 1” is “Bangalore.”
• String 2: This is the substring to be found. For example, if the character “a” is to be found in the word “Bangalore,”“string 2” is “a.”
• Compare: This is the type of comparison to be performed. The types of comparison methods are shown in the following image.

The three comparison methods are explained as follows:

1. vbBinaryCompare: This is a binary comparison and can be entered as zero (0). It is a case-sensitive search of the substring (string 2) in the actual string (string 1).

For example, if 0 is specified in the argument and:

a. The character “a” is to be found in the word “Bangalore,” the output is 2.

b. The character “A” is to be found in the word “Bangalore,” the output is 0. This is because the supplied string is in uppercase which is not found in “string 1.”

2. vbTextCompare: This is a textual comparison and can be entered as one (1). It is a case-insensitive search of the “string 2” in the “string 1.”

For example, if 1 is specified in the argument and:

a. The character “a” is to be found in the word “Bangalore,” the output is 2.

b. The character “A” is to be found in the word “Bangalore,” the output is 2. This is because this comparison method ignores the casing of the substring.

3. vbDatabaseCompare: This can be entered as two (2). It compares based on the information of the Microsoft Access database.

The “string 1” and “string 2” are required arguments, while “start” and “compare” are optional.

Note 1: If the “start” parameter is omitted, the default is 1, implying that the search begins from the first position.

Note 2: If the “compare” parameter is omitted, the default method is “vbBinaryCompare.”

Example #1–“Start” Argument is Omitted

We have to find the position of character “a” in the word “Bangalore.”

Step 1: Enter the following code.

Sub Instr_Example1()

Dim i As Variant

i = InStr("Bangalore", "a")

MsgBox i

End Sub

Step 2: Press F5 or run the VBA code manually, as shown in the following image.

Step 3: The output is 2, as shown in the following image. Hence, the character “a” is at the second position in the word “Bangalore.”

Example #2–“Start” Argument is Specified

We have to find the position of character “a” in the word “Bangalore.” The search should begin from the third position.

Step 1: Enter the following code.

Sub Instr_Example2()

Dim i As Variant

i = InStr(3, "Bangalore", "a")

MsgBox i

End Sub

Step 2: Press F5 or run the VBA code manually, as shown in the following image. 

Step 3: The output is 5, as shown in the following image. Since the search begins from the third letter (n), the VBA InStr function in excel ignores the first occurrence (second position) of the character “a.”

Hence, in this case, the character “a” is at the fifth position in the word “Bangalore.”

Example #3–Case-sensitive Search

We have to find the character “A” in the word “Bangalore.”

Let us supply the compare argument “vbBinaryCompare” to the VBA InStr function.

Step 1: Enter the following code.

Sub Instr_Example3()

Dim i As Variant

i = InStr(1, "Bangalore", "A", vbBinaryCompare)

MsgBox i

End Sub

Step 2: Press F5 or run the VBA code manually, as shown in the following image. 

Step 3: The output is 0, as shown in the following image. Since the argument “vbBinaryCompare” is supplied, the VBA InStr function in excel searches for the uppercase letter “A.”

Hence, the function returns 0 because it could not find the uppercase letter “A” in the word “Bangalore.”

Example #4–Case-insensitive Search

We have to find the character “A” in the word “Bangalore” using the case-insensitive approach.

Let us supply the compare argument “vbTextCompare” to the VBA InStr function.

Step 1: Enter the following code.

Sub Instr_Example4()

Dim i As Variant

i = InStr(1, "Bangalore", "A", vbTextCompare)

MsgBox i

End Sub

Step 2: Press F5 or run the VBA code manually, as shown in the following image.

Step 3: The output is 2, as shown in the following image. Since the argument “vbTextCompare” is supplied, the InStr function ignores the casing of the substring “A.”

Hence, the function returns 2 because the letter “A” or “a” is present at the second position in the word “Bangalore.”

Example #5–Advanced Level

Let us consider an example of the advanced level of VBA InStr function in excel.

The succeeding image shows five worksheets in Excel with the names, “Data,” “Summary 1,” “Summary 2,” “Summary 3,” and “Summary 4.”

We want to hide all worksheets except for the sheet “Data.”

Step 1: Enter the following code to hide all those sheets which contain the word “Summary” in their name.

Sub To_Hide_Specific_Sheet()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets

If InStr(Ws.Name, "Summary") > 0 Then
Ws.Visible = xlSheetVeryHidden
End If

Next Ws
'InStr function looks for word or phrase in the sheet name
'If it finds then it will be hidden
End Sub

Step 2: Press F5 or run the VBA code manually, as shown in the following image. In the output, only the sheet “Data” is visible. The remaining four sheets are hidden.

Likewise, we can unhide those sheets which contain the word “Summary” in their name.

Step 1: Enter the following code to unhide all the sheets.

Sub To_UnHide_Specific_Sheet()

Dim Ws As Worksheet

For Each Ws In ActiveWorkbook.Worksheets

If InStr(Ws.Name, "Summary") > 0 Then
Ws.Visible = xlSheetVisible
End If

Next Ws
'InStr function looks for word or phrase in the sheet name
'If it finds then it will be hidden
End Sub

Step 2: Press F5 or run the VBA code manually, as shown in the following image. In the output, all the five sheets are unhidden.

The properties of the function are listed as follows:

• It is a case-sensitive function. To eliminate this issue, supply the “compare” argument “vbTextCompare.”
• It is a VBA function and cannot be used like other in-built formulas of Excel.
• It returns zero if it cannot find “string 2” in “string 1.”