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