The InStr function returns the position where a search string first appears within another string. It’s essential for string validation, data extraction, conditional branching, and many other scenarios.
Basic Syntax
InStr Function (Search from Beginning)
InStr([start], string, search_string, [compare])
InStrRev Function (Search from End)
InStrRev(string, search_string, [start], [compare])
Parameters (InStr)
| Parameter | Type | Required | Description |
|---|---|---|---|
| start | Long | No | Starting position (defaults to 1) |
| string | String | Yes | The string to search in |
| search_string | String | Yes | The string to search for |
| compare | VbCompareMethod | No | Case sensitivity setting |
Return Value
- If found: Position (starting from 1)
- If not found: 0
Caution
InStr positions start at 1 (not 0). When not found, it returns 0. Use conditional checks like If position > 0 Then to verify the result.
Basic Usage
Simple Search
Sub InStrBasicExample()
Dim text As String
Dim position As Long
text = "Hello World, Welcome to VBA"
' Find "World"
position = InStr(text, "World")
Debug.Print position ' 7 (starts at character 7)
' When not found
position = InStr(text, "Python")
Debug.Print position ' 0
End Sub
Specifying Start Position
Sub InStrWithStartPosition()
Dim text As String
Dim position As Long
text = "192.168.1.1"
' Find first "."
position = InStr(text, ".")
Debug.Print "First dot: " & position ' 4
' Find second "." (start after first dot)
position = InStr(position + 1, text, ".")
Debug.Print "Second dot: " & position ' 8
' Find third "."
position = InStr(position + 1, text, ".")
Debug.Print "Third dot: " & position ' 10
End Sub
Checking if String Contains Text
Sub CheckStringContains()
Dim email As String
email = "user@example.com"
' Validate email (contains @)
If InStr(email, "@") > 0 Then
Debug.Print "Possibly valid email address"
Else
Debug.Print "Invalid email address"
End If
' Check for specific domain
If InStr(email, "example.com") > 0 Then
Debug.Print "This is an example.com domain"
End If
End Sub
Case-Insensitive Search
Sub InStrCaseInsensitive()
Dim text As String
Dim position As Long
text = "Hello World"
' Case-sensitive (default when Option Compare is Binary)
position = InStr(text, "world")
Debug.Print "Case-sensitive: " & position ' 0 (not found)
' Case-insensitive
position = InStr(1, text, "world", vbTextCompare)
Debug.Print "Case-insensitive: " & position ' 7
End Sub
チェック
When you specify the compare argument, you cannot omit the start position (1 above). If you omit compare, the behavior follows the module’s Option Compare setting (Option Compare Binary, which is case-sensitive, if unspecified).
InStrRev: Search from End
InStrRev searches from the end of the string. It’s particularly useful for extracting file names from paths.
Sub InStrRevExample()
Dim filePath As String
Dim position As Long
Dim fileName As String
Dim folderPath As String
filePath = "C:\Users\Username\Documents\report.xlsx"
' Find last "\" from end
position = InStrRev(filePath, "\")
' Extract file name
fileName = Mid(filePath, position + 1)
Debug.Print "File name: " & fileName ' report.xlsx
' Extract folder path
folderPath = Left(filePath, position - 1)
Debug.Print "Folder: " & folderPath ' C:\Users\Username\Documents
End Sub
Tip
InStrRev makes it easy to extract file names from paths. Since it searches from the end, it finds the last separator character efficiently.
Practical Examples
Email Validation
Function IsValidEmail(email As String) As Boolean
' Basic email validation
If Len(email) = 0 Then
IsValidEmail = False
Exit Function
End If
' Check for @
If InStr(email, "@") = 0 Then
IsValidEmail = False
Exit Function
End If
' Check for .
If InStr(email, ".") = 0 Then
IsValidEmail = False
Exit Function
End If
' Check @ appears before last .
Dim atPos As Long
Dim dotPos As Long
atPos = InStr(email, "@")
dotPos = InStrRev(email, ".")
If atPos > dotPos Then
IsValidEmail = False
Exit Function
End If
IsValidEmail = True
End Function
Sub TestEmailValidation()
Debug.Print IsValidEmail("user@example.com") ' True
Debug.Print IsValidEmail("invalid-email") ' False
Debug.Print IsValidEmail("no@dot") ' False
End Sub
Counting Occurrences
Function CountOccurrences(text As String, searchFor As String) As Long
Dim count As Long
Dim position As Long
count = 0
position = InStr(text, searchFor)
Do While position > 0
count = count + 1
position = InStr(position + 1, text, searchFor)
Loop
CountOccurrences = count
End Function
Sub TestCountOccurrences()
Dim text As String
text = "The quick brown fox jumps over the lazy dog"
Debug.Print "Count of 'the': " & CountOccurrences(LCase(text), "the") ' 2
Debug.Print "Count of 'o': " & CountOccurrences(text, "o") ' 4
End Sub
Extracting Substrings Between Delimiters
Function ExtractBetween(text As String, startDelim As String, endDelim As String) As String
Dim startPos As Long
Dim endPos As Long
startPos = InStr(text, startDelim)
If startPos = 0 Then
ExtractBetween = ""
Exit Function
End If
startPos = startPos + Len(startDelim)
endPos = InStr(startPos, text, endDelim)
If endPos = 0 Then
ExtractBetween = ""
Exit Function
End If
ExtractBetween = Mid(text, startPos, endPos - startPos)
End Function
Sub TestExtractBetween()
Dim html As String
html = "<title>My Page Title</title>"
Debug.Print ExtractBetween(html, "<title>", "</title>") ' My Page Title
End Sub
Finding All Positions
Sub FindAllPositions()
Dim text As String
Dim searchFor As String
Dim position As Long
Dim positions As String
text = "The cat sat on the mat with another cat"
searchFor = "cat"
positions = ""
position = InStr(text, searchFor)
Do While position > 0
If positions <> "" Then positions = positions & ", "
positions = positions & position
position = InStr(position + 1, text, searchFor)
Loop
Debug.Print "'" & searchFor & "' found at positions: " & positions
' 'cat' found at positions: 5, 36
End Sub
Summary
InStr and InStrRev are fundamental string search functions in VBA:
- InStr: Search from beginning, great for validation and extraction
- InStrRev: Search from end, ideal for path parsing
- Returns 0 when not found—always check the result
- Use
vbTextComparefor case-insensitive searches - Combine with Mid, Left, Right for powerful string manipulation
Master these functions to efficiently handle string operations in your VBA projects.