InStr Function in Excel VBA

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.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: VBA InStr (wallstreetmojo.com)

The Syntax of the VBA InStr Function

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.”

  1. 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:

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.

  1. 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.”

VBA InStr Examples

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 codeVBA CodeVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more 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.

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.”

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

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

Sub Instr_Example3()

Dim i As Variant

i = InStr(1, “Bangalore”, “A”, vbBinaryCompare)

MsgBox i

End Sub

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.”

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.

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 sheetsUnhide Those SheetsThere are different methods to Unhide Sheets in Excel as per the need to unhide all, all except one, multiple, or a particular worksheet. You can use Right Click, Excel Shortcut Key, or write a VBA code in Excel. read more 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.

Properties of VBA InStr Function

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 functionVBA FunctionVBA functions serve the primary purpose to carry out specific calculations and to return a value. Therefore, in VBA, we use syntax to specify the parameters and data type while defining the function. Such functions are called user-defined functions.read more and cannot be used like other in-built formulas of Excel.It returns zero if it cannot find “string 2” in “string 1.”

Frequently Asked Questions

This has been a guide to VBA InStr Function in Excel. Here we learn how to use the InStr function along with step by step examples and a downloadable excel template. Below you can find some useful Excel VBA articles-

The VBA InStr function returns the position of one string within another string. This position corresponds to the first occurrence of the substring. The function returns an integer as the output. It returns zero (0) if the substring is not found within the string. The syntax and the arguments of the function are listed as follows: “InStr([start],string1,string2,[compare])”• Start: It specifies the position from which search should begin. The default value is 1. • String 1: It is the actual string within which the substring is to be searched. • String 2: It is the substring to be searched. • Compare: It specifies the comparison method to be used. The methods are stated as follows: a. vbBinaryCompare or 0: It is used for a case-sensitive search of the substring within the string. b. vbTextCompare or 1: It is used for a case-insensitive search of the substring within the string. c. vbDatabaseCompare or 2: It is used for comparison with Microsoft Access database. The arguments “string 1” and “string 2” are mandatory, while “start” and “compare” are optional.

The differences between the two functions are stated as follows:• The VBA InStr searches from left to right while the VBA InStrRevVBA InStrRevVBA INSTRREV Function or reverse string function returns the position of one string which occurs within the other. It sorts starting from the end of the string (from right to left) from which we are looking for a searchable string.read more searches from right to left. • The syntax of VBA InStr is “InStr([start],string1,string2,[compare]).” In comparison, the syntax of InStrRev is “InStrRev(string1,string2,[start,[compare]]).” • If the “start” argument is omitted, the InStr begins to search from the starting (first position) of the string. In contrast, the InStrRev begins to search from the end (last position) of the string.

With the usage of wildcards, the InStr function returns “true” or “false” depending on whether it has found the specified substring within the string or not. The function supports the usage of the following wildcards: 1. Asterisk (): It represents one or more characters of a string and works as follows: • “a” refers to the text that begins with the character “a.” • “*a” refers to the text that ends with the character “a.” • “a” refers to the text that has the character “a” in the middle. 2. Question mark (?): It represents one character of a string and works as follows: • “a?” refers to two characters beginning with “a.” • “?a” refers to two characters ending with “a.” • “?a?” refers to three characters having “a” in the middle. Likewise, the VBA InStr function can be used with the tilde (~) as well.

  • VBA FileCopy FunctionExcel VBA FileDialogExcel VBA String FunctionsExcel VBA SubStringVBA Name Worksheet