Introduction to InStr Function in Excel VBA
The InStr function in Excel VBA allows you to find the position of specific strings within a given text.
Generic Syntax
InStr([start], string1, string2, [compare])Where,
| Arguments | Required/ Optional | Definition |
|---|---|---|
| start | Optional | Starting position of the search.
|
| string1 | Required | The string to search in, Primary String. |
| string2 | Required | The string to search for in the Primary String. |
| compare | Optional | The InStr function is case-sensitive by default. But if you want to run a case insensitive InStr, then you can pass the argument here to perform a certain comparison. This argument can be the following values,
By default, InStr takes vbBinaryCompare as the compare argument. |
Example 1 – Find Position of Text in a String
- Open the Visual Basic Editor by pressing Alt + F11.
- Insert a new module.
- Enter the following code inside a VBA Sub Procedure:
Sub INSTR_Example()
MsgBox InStr("Happiness is a choice", "choice")
End Sub
- Run the code (F5 or Run Sub/UserForm).
- The message box will display the position of the word choice (which is 16 in this case).
Explanation
Our primary string, “Happiness is a choice” is a 21-letter sentence (with spaces) and we wanted to find the position of the text “choice” in that string. The text “choice” started from the 16th position of the primary string; hence we got number 16 as our output in the message box.
Example 2 – Find Text from a Specific Position in a String
- Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
- Modify the code as follows:
Sub INSTR_Example()
MsgBox InStr(17, "Happiness is a choice", "choice")
End Sub
- Run the code.
Running this code will give you the position of the second occurrence of choice (which is 27).
Explanation
As we already know (from the phase 1 discussion) that the text “choice” started from the position of 16, so we inserted two “choice” in the primary string and set 17 as our 1st parameter to skip the first “choice”. So, we Run the above macro and it showed us position number 27 which is exactly the position number of the second “choice” in the given string.
Read More: How to Find String in a Cell Using VBA in Excel
Example 3 – Case-insensitive Search
We wanted to find the position of the word “Choice” with a capital “C” in the string “Happiness is a choice” where choice is written with a small “c”.
- By default, InStr is case-sensitive.
- To make it case-insensitive, use the vbTextCompare argument:
Sub INSTR_Example()
MsgBox InStr(1, "Happiness is a choice and choice", "Choice", vbTextCompare)
End Sub
- Run the code.
Now it will find Choice regardless of capitalization.
You will get the position of the text from the string, whether the text is written in capital letters or small letters.
Example 4 – Find Text from the Right of a String
Until now, the InStr function only provided the position from the left side of a string. But what if you need to find the text position from the right side? Enter the InStrRev function, which searches from the right. It works similarly to InStr but finds the position of a text from the right side of the string. Let’s compare the following examples:
- If we run the following code with the InStr function then,
it gives us the position (16) of the first occurence of the text “choice”.
- However, if we use InStrRev,
it gives us the position (27) of the last occurrence of “choice“.
Example 5 – Find the Position of a Character in String
You can also find the position of a specific character in a string. For instance, consider the following VBA code snippet:
Sub Find_Character()
Dim z As Long
z = InStr("Happiness is a choice", "e")
MsgBox z
End Sub
- When you run this macro, it will return the position of the first “e” in the given string (which is at position 7).
Example 6 – Find a Substring in a String
To determine whether a string contains a specific substring, you can use an IF Statement. Here’s an example:
Public Sub FindSub()
If InStr("Happiness is a choice", "choice") = 0 Then
MsgBox "No match found"
Else
MsgBox "Match found"
End If
End Sub
Running this macro will display Match found because the primary string Happiness is a choice contains the word choice.
Example 7 – Find a String in a Cell Range
Suppose you want to search for a certain text in a cell range and return a specific string. Consider the following code:
Sub Find_String_in_Range()
Dim cell As Range
For Each cell In Range("B5:B10")
If InStr(cell.Value, "Dr.") > 0 Then
cell.Offset(0, 1).Value = "Doctor"
End If
Next cell
End Sub
- Run the code and the result is shown below.
Running this code will find instances of Dr. in the specified cell range and replace them with Doctor.
Example 8 – Find String in a Cell
If you want to search for a specific text in a single cell and return a certain string, use the following code:
Sub Find_String_in_Cell()
If InStr(Range("B5").Value, "Dr.") > 0 Then
Range("C5").Value = "Doctor"
End If
End Sub
This macro searches for “Dr.” in Cell B5 and, if found, replaces it with “Doctor” in Cell C5. You can adapt the macro for other search terms and replacements as needed. For example, to find “Prof.” and replace it with “Professor,” adjust the values accordingly.
Download Practice Workbook
You can download the practice workbook from here:
