How To Use Excel VBA InStr Function? (Examples) - WallStreetMojo

All Courses LoginAll BlogsVBA ResourcesVBA InStrVBA InStr

Publication Date :

30 Jun, 2021

Blog Author :

Jeevan A Y

Edited by :

Vandana Kataria

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Share

Python + AI = Career BoostDownload FREE VBA InStr In Excel Template and Follow Along!VBA Instr Function Excel Template.xlsm

Table Of Contents

InStr Function in Excel VBA

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.

🐍 Build Smarter Python Code with ChatGPT & AI Integration

Want to code faster and smarter? Build Python Programs with ChatGPT — teaches you how to use AI to write, debug, and optimize Python scripts with ease. Learn to automate coding tasks, integrate APIs, and build real-world applications—perfect for beginners, developers, and tech-savvy professionals looking to supercharge their productivity with AI.

Learn More →

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

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

Build Python Programs with ChatGPT - Automate Coding & AI Integration Learn to build real-world Python projects faster using the power of ChatGPT. Automate code generation, debugging, and AI-driven problem solving while mastering core Python skills. Earn a recognized certification and gain hands-on experience that’s perfect for developers, analysts, and anyone exploring the intersection of coding and AI. Learn More →

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

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 function 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 (FAQs)

VBA InStr FAQs1

Define the VBA InStr function.

2

What is the difference between the InStr and the InStrRev functions of VBA?

3

How to use the VBA InStr function in excel with wildcards?

View all FINANCIAL MODELING 2-DAY BOOTCAMP @70% OFF – YEAR END SALE

Master Financial Modeling in Just 2 Days | Build and Forecast IS, BS & CF from Scratch | Perfect for Graduates and Professionals Looking to Upskill.

Join WallStreetMojo YouTube

INVESTMENT BANKING MASTERY PROGRAM @70% OFF – YEAR END SALE

Experience the Exact Training Used by Top Investment Banks | Learn FM, DCF, LBO, M&A, Accounting & More | Unlock Wall Street-Level Skills.

Join WallStreetMojo Instagram

CHATGPT & AI FOR EXCEL COURSE @70% OFF – YEAR END SALE

Boost Productivity 10X with AI-Powered Excel | Automate Reports, Eliminate Errors & Advance Your Analytics Skills | Transform Your Workflow.

Join WallStreetMojo LinkedIn

EXCEL ALL-IN-ONE BUNDLE COURSE @70% OFF – YEAR END SALE

Master Excel, VBA & Power BI Like a Pro | 70+ Hours of Expert-Led Training | Learn Real-World Applications & Earn Your Certification.

Join WallStreetMojo Facebook

Join WallStreetMojo X