InStr Function

Maintained on

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)

ParameterTypeRequiredDescription
startLongNoStarting position (defaults to 1)
stringStringYesThe string to search in
search_stringStringYesThe string to search for
compareVbCompareMethodNoCase sensitivity setting

Return Value

  • If found: Position (starting from 1)
  • If not found: 0
チェック

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

instr_basic.bas
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

instr_start_position.bas
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

instr_contains.bas
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
instr_case_insensitive.bas
Sub InStrCaseInsensitive()
    Dim text As String
    Dim position As Long

    text = "Hello World"

    ' Case-sensitive (default)
    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

InStrRev: Search from End

InStrRev searches from the end of the string. It’s particularly useful for extracting file names from paths.

instrrev_example.bas
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
チェック

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

instr_validate_email.bas
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

instr_count_occurrences.bas
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

instr_extract_between.bas
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

instr_find_all.bas
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 vbTextCompare for case-insensitive searches
  • Combine with Mid, Left, Right for powerful string manipulation

Master these functions to efficiently handle string operations in your VBA projects.

#VBA #InStr #InStrRev #String Operations #Search
Previous Page
How to Use the GoTo Command to Jump to Specific Points in Code
Next Page
How to Close a Workbook Without Saving Using VBA with Examples