InStr Function in Excel VBA
Use InStr in Excel VBA to find the position of a substring in a string. The InStr function is quite versatile.
Place a command button on your worksheet and add the code lines below. To execute the code lines, click the command button on the sheet.
Simple InStr Function
By default, the InStr function starts searching at the beginning of the string (position 1).
Code:
state = "Virginia"
MsgBox InStr(state, "gin")
Result:
Note: string "gin" found at position 4.
Start Position
The second InStr function below starts searching at position 7.
Code:
state = "South Carolina"
MsgBox InStr(state, "o")
MsgBox InStr(7, state, "o")
Result:
Explanation: the first InStr function finds the string "o" at position 2. The second InStr function starts searching at position 7 and finds the string "o" at position 10.
Zero
The InStr function returns 0 if the string is not found (important as we will see next).
Code:
state = "Florida"
MsgBox InStr(state, "us")
Result:
Conclusion: string "us" not found.
InStr and If
Let's create a simple VBA program that uses the InStr function.
Code:
state = Range("A2").Value
substring = Range("B2").Value
If InStr(state, substring) > 0 Then
Range("C2").Value = "Found"
Else
Range("C2").Value = "Not Found"
End If
Result when you click the command button on the sheet:
Explanation: string "outh" found at position 2. The InStr function returns 2. As a result, Excel VBA places the string "Found" into cell C2.
Case-insensitive Search
By default, the InStr function performs a case-sensitive search. Enter the string "dakota" into cell B2 and click the command button on the sheet.
Explanation: string "dakota" not found (first letter not capitalized). The InStr function returns 0. As a result, Excel VBA places the string "Not Found" into cell C2.
To perform a case-insensitive search, update the code as follows:
state = Range("A2").Value
substring = Range("B2").Value
If InStr(1, state, substring, vbTextCompare) > 0 Then
Range("C2").Value = "Found"
Else
Range("C2").Value = "Not Found"
End If
Result when you click the command button on the sheet:
Explanation: the InStr function shown above has 4 arguments. To perform a case-insensitive search, always specify a start position (first argument) and use vbTextCompare (fourth argument).
If you're new here, welcome to Excel Easy! Join over 1 million monthly Excel learners. You can find popular courses here: Data Analysis in Excel and Excel VBA.