How to use INSTR Function to Find Text in Excel VBA?

In this post, you’ll learn how to use INSTR function function to find a text with-in your Excel spreadsheet using Excel VBA.

INSTR Function in Excel VBA

The VBA Instr Function in VBA is used to check whether a string of text is found in another string of text. When the text is not found It returns a Boolean value 0. Else, it returns the character position where the text is found.

Similar to the Like Operator in VBA, the Instr function can be used instead to perform inexact matches/pattern matching.

Syntax for Instr function:

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

[start]  – This is the starting position of the search and it is optional. Enter 1 to start searching from position 1 (by default it takes 1 as the start position). Enter 7 to start searching from position 7

string – The text string to be searched.

substring – The string of text to find in the primary string.

[compare]  – Instr is case-sensitive in default. You can make Instr Case insensitive by setting this argument.

Find Text String in a Cell the INSTR function

To find the text string in a cell using the Instr function,

Code:

Sub StringCell()

    If InStr(Range("A2").Value, "Er.") > 0 Then

        Range("D2").Value = "Engineer"

    End If

End Sub

Or you can loop through a range of cells

Sub StringCell()

Dim block As Range

    For Each block In Range("A1:A10")

        If InStr(block.Value, "Er") > 0 Then

            block.Offset(0, 1).Value = "Engineer"

        End If

    Next cell

End Sub

Perform a case-insensitive search with INSTR function.

As we know,  VBA is case-sensitive, which mean it considers “A” different from “a”. Set the [compare] argument to 1 or vbTextCompare in order to make VBA case-insensitive,

Code:

Public Sub InSensitiveCase()

  MsgBox InStr(1, "Visit us at Developerpublish.com", "visit", vbTextCompare)

End Sub

Alternatively, you can add Option Compare Text to the top of your code module.

Option Compare Text will impact all of the code in that module.