VBA INSTR Function

VBA InStr Function

Objective of the VBA InStr Function:

VBA InStr function is categorized as a Text/String function in VBA. It is a built-in function in MS Office Excel. It finds the position of specified sub-string with the given string. It returns the first occurrence position as a integer value. If it doesn’t find specified sub string, It returns a value ‘0’. The InStr function performs a case sensitive search. It has four parameters. The VBA InStr function can be used in either procedure or function in a VBA editor window in Excel. We can use this VBA InStr function any number of times in any number of procedures or functions. In the following section we learn what is the syntax and parameters of the InStr function, where we can use this VBA InStr function and real-time examples.

Table of Contents:

Syntax of VBA InStr Function:

The syntax of the VBA InStr function is

InStr ([start], string, substring, [compare])

In the above syntax the second and third arguments are mandatory (string & substring) parameters. And the first and last two parameters are optional (start & compare) Arguments.

Parameters or Arguments:

Where
Start: The start is an optional argument, and its default value is ‘1’. It represents the starting position for the search. It accepts integer type input value. If we don’t specify any value, default it considers default value as one.
String: The string is a mandatory argument. The string in which we want to search.
Substring: The substring is a mandatory argument. The substring that you want to find within string.
Compare: The compare is an optional argument. It represents which type of comparison needs to perform. It has four numeric values. It we don’t specify any value, default it considers a binary comparison. Here are the different four comparisons, which are shown in the below table.

VBA Compare Value Explanation
vbUseCompareOption -1 Performs a comparision using the ‘option compare’
vbBinaryCompare 0 Performs a Binary comparison
vbTextCompare 1 Performs a Textual comparison
vbDatabaseCompare 2 Microsoft Access only. Performs a comparison based on information in your database.

Where we can apply or use the VBA InStr Function?

We can use this VBA InStr function in MS Office 365, MS Excel 2016, MS Excel 2013, 2011, Excel 2010, Excel 2007, Excel 2003, Excel 2016 for Mac, Excel 2011 for Mac, Excel Online, Excel for iPhone, Excel for iPad, Excel for Android tablets and Excel for Android Mobiles.

Example 1: Search specified substring in given string, starting at position 1

Here is a simple example of the VBA InStr function. This below example macro uses the InStr function and finds specified substring in within given string, starting at position 1

'Search for "if" in string "Life is Beautiful", starting at position 1.
Sub VBA_InStr_Function_Ex1()
    
    'Variable declaration
    Dim iPosition As Integer
    Dim sWord As String
    
    sWord = "Life is Beautiful"
    
    'Search for 'if' in given string
    iPosition = InStr(1, sWord, "if")
    'or
    iPosition = InStr(sWord, "if")
        
    'You can see answer in the Worksheet
    Sheets("VBAF1.com").Range("F8") = "The text 'if' position : " & iPosition
    
    'Display output message
    MsgBox "The text 'if' position: " & iPosition, vbInformation, "Example of InStr Function"
    
End Sub

In the above example ‘iPosition & sWord’ are declared as an integer and string data type. This variable ‘iPosition’ now contains the starting position of substring.
Output:
Here is the screen shot of first example output.
VBA InStr Function

Example 2: Search specified substring within string, starting at position 3

Here is another example of the VBA InStr function. This below example macro uses the InStr function and finds specified substring in within given string, starting at position 3

'Search for "if" in string "Life is Beautiful", starting at position 3.
Sub VBA_InStr_Function_Ex2()
    
    'Variable declaration
    Dim iPosition As Integer
    Dim sWord As String
    
    sWord = "Life is Beautiful"
    
    'Search for 'if' in given string
    iPosition = InStr(3, sWord, "if")
        
    'You can see answer in the Worksheet
    Sheets("VBAF1.com").Range("F11") = "The text 'if' position : " & iPosition
    
    'Display output message
    MsgBox "The text 'if' position : " & iPosition, vbInformation, "Example of InStr Function"
    
End Sub

Output:
Here is the screen shot of second example output.
VBA InStr Function

Example 3: InStr Function using ‘vbBinaryCompare’ (Case Sensitive Search)

Here is a simple example of the VBA InStr function. This below example macro uses the InStr function and finds ‘b’ substring in within given string, starting at position 1. Where ‘b’ is a small letter. It returns ‘0’ as an output. Notice fourth argument in InStr function. It performs a binary comparison.

'Search for "b" in string "Life is Beautiful", starting at position 1.
Sub VBA_InStr_Function_Ex3()
    
    'Variable declaration
    Dim iPosition As Integer
    Dim sWord As String
    
    sWord = "Life is Beautiful"
    
    'Search for 'b' in given string
    iPosition = InStr(1, sWord, "b")
    ‘or
   ‘iPosition =InStr(1, sWord, "b", vbBinaryCompare)

        
    'You can see answer in the Worksheet
    Sheets("VBAF1.com").Range("F14") = "The text 'b' position : " & iPosition
    
    'Display output message
    MsgBox "The text 'b' position : " & iPosition, vbInformation, "Example of InStr Function"
    
End Sub

Note: VBA InStr is a Case sensitive search.
Output:
Here is the screen shot of third example output.
VBA InStr Function

Example 4: InStr Function using vbTextCompare (Avoid Case Sensitive Search)

Here is one more example of the VBA InStr function. This below example macro uses the InStr function and finds ‘b’ substring in within given string, starting at position 1. Where ‘b’ is a small letter, but It avoids case sensitive search and returns ‘9’ as an output. Notice fourth argument in InStr function. It performs a textual comparison.

'Search for "b" in string "Life is Beautiful", starting at position 1.
Sub VBA_InStr_Function_Ex4()
    
    'Variable declaration
    Dim iPosition As Integer
    Dim sWord As String
    
    sWord = "Life is Beautiful"
    
    'Search for 'b' in given string
    iPosition = InStr(1, sWord, "b", vbTextCompare)
        
    'You can see answer in the Worksheet
    Sheets("VBAF1.com").Range("F17") = "The text 'b' position : " & iPosition
    
    'Display output message
    MsgBox "The text 'b' position : " & iPosition, vbInformation, "Example of InStr Function"
    
End Sub

Output:
Here is the screen shot of fourth example output.
VBA InStr Function

Example 5: Search for ‘@’ symbol in specified email

Here is a simple example of the VBA InStr function. This below example macro uses the InStr function and finds specified substring in within given string, starting at position 1. We can also search special characters within string. Sometimes we want to search ‘@’ position in email. Let’s see.

'Search for "@" in string "abcde@gmail.com", starting at position 1.
Sub VBA_InStr_Function_Ex5()
    
    'Variable declaration
    Dim iPosition As Integer
    Dim sWord As String
    
    sWord = "abcde@gmail.com"
    
    'Searh for '@' in given string
    iPosition = InStr(1, sWord, "@")
        
    'You can see answer in the Worksheet
    Sheets("VBAF1.com").Range("I8") = "The Special Character '@' position : " & iPosition
    
    'Display output message
    MsgBox "The Special Character '@' position : " & iPosition, vbInformation, "Example of InStr Function"
    
End Sub

Output:
Here is the screen shot of fifth example output.
VBA InStr Function

Example 6: Search for ‘.’ and file extension within specified file name

Here is a simple example of the VBA InStr function. This below example macro uses the InStr function and finds specified substring(.) in within given string, starting at position 1. Most of the time while writing VBA codes we try to extract file extension or file name. Before that we need to identify ‘.’ Position In file name.

'Search for '.' and file extension in the file name "xyz.xlsm"
Sub VBA_InStr_Function_Ex6()
    
    'Variable declarations
    Dim iPosition As Integer
    Dim sWord As String
    Dim sFileExtn As String
    
    sWord = "xyz.xlsm"
    
    'Search file extension in given string
    iPosition = InStr(1, sWord, ".")
    
    'Get File Extension
    sFileExtn = Right(sWord, Len(sWord) - iPosition)
        
    'You can see answer in the Worksheet
    Sheets("VBAF1.com").Range("I11") = "Specified File Extension is : " & sFileExtn
    
    'Display output message
    MsgBox "Specified File Extension is : " & sFileExtn, vbInformation, "Example of InStr Function"
    
End Sub

Output:
Here is the screen shot of sixth example output.
VBA InStr Function

Instructions:

Here are the instructions to use the VBA InStr function in Excel.

  • Open an Excel workbook.
  • Press Alt+F11 to open VBA Editor window.
  • Go to Insert menu and click on module from the available options.
  • Insert above specified macro example(s).
  • Select code and click on ‘Run’ command or use Keyboard shortcut ‘F5’.
  • You can see output on the Excel sheet and displays on the screen as well. Please refer above output screen shots for your reference.

List of VBA Functions:

Please click on below link to go back to see all List of VBA Functions.

List of VBA Functions

Leave a Comment

Your email address will not be published. Required fields are marked *