What the InStr VBA Function Does
The InStr VBA function searches for a specific text string within another string and tells you where it starts. Think of it like using Ctrl+F to find a word in a document, except VBA does it automatically in your code.
You’ll often need to check if certain text exists in cells or extract specific information from longer strings. The InStr function makes this possible by returning the position number where your search text begins. If it doesn’t find what you’re looking for, it returns 0.
Understanding the InStr Syntax
InStr([Start], String1, String2, [Compare])The brackets around some arguments mean they’re optional, so you don’t always need to include them.
| Argument | Required? | What It Means | When to Use It / Options |
|---|---|---|---|
|
Start |
No |
A number that represents where you want to start looking in the string. For example, if you set Start to 5, VBA ignores the first four characters and begins searching from the fifth character onward. |
This comes in handy when you need to find the second or third occurrence of text, or when you want to skip a known portion of the string. |
|
String1 |
Yes |
The complete text string that contains the information you want to search through. This could be a cell value, a variable, or any text expression. |
This is always required as it’s the text you’re searching within. |
|
String2 |
Yes |
The specific text you want to locate. This could be a single character, a word, or even a longer phrase. |
This is always required as it’s what you’re actually searching for within String1. |
|
Compare |
No | This controls how VBA compares text during the search. You have three options:
|
If you don’t specify this argument, VBA uses the Option Compare setting at the top of your module, which defaults to binary (case-sensitive) if nothing is set. |
Tips and Tricks for Using InStr
Always check if InStr returns 0 before proceeding with your code. A zero result means the text wasn’t found, and trying to use that result in calculations can cause errors.
Remember that InStr returns the position from the beginning of the original string, not from your Start position. If you start at character 10 and find a match at position 15, InStr returns 15, not 5.
- Always check if InStr returns 0 before proceeding with your code. A zero result means the text wasn't found, and trying to use that result in calculations can cause errors.
- Remember that InStr returns the position from the beginning of the original string, not from your Start position. If you start at character 10 and find a match at position 15, InStr returns 15, not 5.
Real-World Examples
Finding Email Domains
Let’s say you’re managing a contact list and need to identify which emails belong to a specific company. Here’s how you’d use the InStr function to check if an email address contains a particular domain:
Sub FindEmailDomain()
Dim emailAddress As String
Dim position As Integer
emailAddress = Range("A1").Value
position = InStr(1, emailAddress, "@company.com", vbTextCompare)
If position > 0 Then
MsgBox "This is a company email address"
Else
MsgBox "This is not a company email address"
End If
End Sub
In this example, we’re checking if cell A1 contains an email with “@company.com” in it. The function searches from the first character (that’s the 1), looks for the domain text, and ignores whether it’s uppercase or lowercase (vbTextCompare). If the position is greater than 0, we know the domain exists in that email address.
Extracting Information from Product Codes
Many businesses use product codes that contain embedded information. Suppose your product codes follow a pattern like “DEPT-CATEGORY-ITEM” and you need to verify if items belong to a specific department:
Sub CheckDepartment()
Dim productCode As String
Dim deptPosition As Integer
Dim dashPosition As Integer
Dim department As String
productCode = Range("B2").Value
' Find the first dash
dashPosition = InStr(1, productCode, "-")
If dashPosition > 0 Then
' Extract everything before the first dash
department = Left(productCode, dashPosition - 1)
' Check if it's the Sales department
If InStr(1, department, "SALES", vbTextCompare) > 0 Then
Range("C2").Value = "Sales Department"
Else
Range("C2").Value = "Other Department"
End If
End If
End Sub
This example shows a more advanced use of the InStr VBA function. First, we locate the dash that separates the department code from the rest. Then we extract the department portion and check if it contains “SALES.” Notice how we use InStr twice: once to find the dash position and again to verify the department name. This demonstrates how you can combine InStr with other string functions like Left to parse structured data.
Additional Considerations
- The InStr function has a related function called InStrRev that searches from right to left instead of left to right. This is useful when you're looking for the last occurrence of text, like finding the final period in a filename to identify the extension.
- When working with large datasets, InStr is much faster than using worksheet functions in loops. If you're processing thousands of rows, moving the logic into VBA with InStr can significantly improve performance.
- If you're dealing with text that might be empty or contain null values, add error handling to your code. Checking if a string is empty before using InStr prevents runtime errors and makes your macros more robust.
