`InStrRev` Function

Returns the position of an occurrence of one string within another, from the end of string.

Syntax

InStrRev(stringcheck, stringmatch[, start[, compare]])

Parameters

stringcheck (Required): String expression being searched
stringmatch (Required): String expression to search for
start (Optional): Numeric expression that sets the starting position for each search. If omitted, -1 is used, which means the search begins at the last character position. If start contains Null, an error occurs
compare (Optional): Numeric value indicating the kind of comparison to use when evaluating substrings. If omitted, a binary comparison is performed

Compare Parameter Values

vbUseCompareOption (-1): Performs a comparison using the setting of the Option Compare statement
vbBinaryCompare (0): Performs a binary comparison (case-sensitive)
vbTextCompare (1): Performs a textual comparison (case-insensitive)
vbDatabaseCompare (2): Microsoft Access only. Performs a comparison based on information in your database

Return Value

Returns a Long: - If stringcheck is zero-length: Returns 0 - If stringcheck is Null: Returns Null - If stringmatch is zero-length: Returns start - If stringmatch is Null: Returns Null - If stringmatch is not found: Returns 0 - If stringmatch is found within stringcheck: Returns position where match begins - If start is greater than length of stringcheck: Search begins at the last character position

Remarks

The InStrRev function searches from the end of the string backward:

Searches from right to left (reverse direction)
Returns the character position of the LAST occurrence (1-based indexing)
Default start position is -1 (end of string) if omitted
Search is case-sensitive by default (vbBinaryCompare)
Use vbTextCompare for case-insensitive searching
Unlike InStr, start parameter comes after the strings being compared
The returned position is still counted from the beginning (1-based), not from the end
Useful for finding file extensions, last delimiters, etc.
More efficient than repeated InStr calls for finding last occurrence
The compare parameter affects performance (binary is faster than text)
Returns 0 if substring not found (test with > 0 for found)

Typical Uses

Find Last Occurrence: Locate the last instance of a substring
File Extensions: Extract file extension by finding last dot
Path Parsing: Find last path separator in file paths
File Names: Extract filename from full path
Domain Extraction: Find last dot in domain names
String Truncation: Find last delimiter for truncation
Reverse Parsing: Parse from end of string
Last Word Extraction: Find last space in sentences

Basic Usage Examples

' Example 1: Find last occurrence
Dim text As String
Dim pos As Long
text = "apple,banana,apple,orange"
pos = InStrRev(text, "apple")
Debug.Print pos  ' Prints: 14 (last apple)

' Example 2: Get file extension
Dim fileName As String
Dim pos As Long
Dim extension As String
fileName = "document.backup.txt"
pos = InStrRev(fileName, ".")
extension = Mid$(fileName, pos)
Debug.Print extension  ' Prints: .txt

' Example 3: Extract filename from path
Dim fullPath As String
Dim pos As Long
Dim fileName As String
fullPath = "C:\Projects\MyApp\MainForm.frm"
pos = InStrRev(fullPath, "\")
fileName = Mid$(fullPath, pos + 1)
Debug.Print fileName  ' Prints: MainForm.frm

' Example 4: Search with start position
Dim text As String
Dim pos As Long
text = "one,two,three,four"
pos = InStrRev(text, ",", 10)  ' Search up to position 10
Debug.Print pos  ' Prints: 8 (comma after "two")

Common Patterns

' Pattern 1: Get file extension
Function GetFileExtension(fileName As String) As String
Dim pos As Long

pos = InStrRev(fileName, ".")
If pos > 0 Then
GetFileExtension = Mid$(fileName, pos + 1)
Else
GetFileExtension = ""
End If
End Function

' Pattern 2: Get filename from path
Function GetFileNameFromPath(fullPath As String) As String
Dim pos As Long

pos = InStrRev(fullPath, "\")
If pos = 0 Then pos = InStrRev(fullPath, "/")

If pos > 0 Then
GetFileNameFromPath = Mid$(fullPath, pos + 1)
Else
GetFileNameFromPath = fullPath
End If
End Function

' Pattern 3: Get directory from path
Function GetDirectoryFromPath(fullPath As String) As String
Dim pos As Long

pos = InStrRev(fullPath, "\")
If pos = 0 Then pos = InStrRev(fullPath, "/")

If pos > 0 Then
GetDirectoryFromPath = Left$(fullPath, pos - 1)
Else
GetDirectoryFromPath = ""
End If
End Function

' Pattern 4: Remove file extension
Function RemoveFileExtension(fileName As String) As String
Dim pos As Long

pos = InStrRev(fileName, ".")
If pos > 0 Then
RemoveFileExtension = Left$(fileName, pos - 1)
Else
RemoveFileExtension = fileName
End If
End Function

' Pattern 5: Get last word
Function GetLastWord(text As String) As String
Dim pos As Long

text = Trim$(text)
pos = InStrRev(text, " ")

If pos > 0 Then
GetLastWord = Mid$(text, pos + 1)
Else
GetLastWord = text
End If
End Function

' Pattern 6: Truncate at last delimiter
Function TruncateAtLastDelimiter(text As String, delimiter As String) As String
Dim pos As Long

pos = InStrRev(text, delimiter)
If pos > 0 Then
TruncateAtLastDelimiter = Left$(text, pos - 1)
Else
TruncateAtLastDelimiter = text
End If
End Function

' Pattern 7: Get parent directory
Function GetParentDirectory(path As String) As String
Dim pos As Long

' Remove trailing slash if present
If Right$(path, 1) = "\" Then
path = Left$(path, Len(path) - 1)
End If

pos = InStrRev(path, "\")
If pos > 0 Then
GetParentDirectory = Left$(path, pos - 1)
Else
GetParentDirectory = ""
End If
End Function

' Pattern 8: Extract domain without subdomain
Function GetRootDomain(url As String) As String
Dim domain As String
Dim pos As Long
Dim dotPos As Long

' Extract domain first (simplified)
pos = InStr(url, "://")
If pos > 0 Then
domain = Mid$(url, pos + 3)
Else
domain = url
End If

' Remove path
pos = InStr(domain, "/")
If pos > 0 Then
domain = Left$(domain, pos - 1)
End If

' Find last two parts (domain.tld)
dotPos = InStrRev(domain, ".")
If dotPos > 0 Then
' Find second-to-last dot
pos = InStrRev(domain, ".", dotPos - 1)
If pos > 0 Then
GetRootDomain = Mid$(domain, pos + 1)
Else
GetRootDomain = domain
End If
Else
GetRootDomain = domain
End If
End Function

' Pattern 9: Replace last occurrence
Function ReplaceLastOccurrence(text As String, findText As String, replaceText As String) As String
Dim pos As Long

pos = InStrRev(text, findText)
If pos > 0 Then
ReplaceLastOccurrence = Left$(text, pos - 1) & replaceText & _
Mid$(text, pos + Len(findText))
Else
ReplaceLastOccurrence = text
End If
End Function

' Pattern 10: Get text before last occurrence
Function GetBeforeLastOccurrence(text As String, delimiter As String) As String
Dim pos As Long

pos = InStrRev(text, delimiter)
If pos > 0 Then
GetBeforeLastOccurrence = Left$(text, pos - 1)
Else
GetBeforeLastOccurrence = text
End If
End Function

Advanced Usage Examples

' Example 1: Path utilities class
Public Class PathUtils
Public Function GetFileName(fullPath As String) As String
Dim pos As Long
pos = InStrRev(fullPath, "\")
If pos = 0 Then pos = InStrRev(fullPath, "/")

If pos > 0 Then
GetFileName = Mid$(fullPath, pos + 1)
Else
GetFileName = fullPath
End If
End Function

Public Function GetFileNameWithoutExtension(fullPath As String) As String
Dim fileName As String
Dim pos As Long

fileName = GetFileName(fullPath)
pos = InStrRev(fileName, ".")

If pos > 0 Then
GetFileNameWithoutExtension = Left$(fileName, pos - 1)
Else
GetFileNameWithoutExtension = fileName
End If
End Function

Public Function GetExtension(fullPath As String) As String
Dim pos As Long
pos = InStrRev(fullPath, ".")

If pos > 0 Then
GetExtension = Mid$(fullPath, pos + 1)
Else
GetExtension = ""
End If
End Function

Public Function GetDirectory(fullPath As String) As String
Dim pos As Long
pos = InStrRev(fullPath, "\")
If pos = 0 Then pos = InStrRev(fullPath, "/")

If pos > 0 Then
GetDirectory = Left$(fullPath, pos - 1)
Else
GetDirectory = ""
End If
End Function

Public Function ChangeExtension(fullPath As String, newExtension As String) As String
Dim pos As Long
pos = InStrRev(fullPath, ".")

If pos > 0 Then
ChangeExtension = Left$(fullPath, pos - 1) & "." & newExtension
Else
ChangeExtension = fullPath & "." & newExtension
End If
End Function
End Class

' Example 2: URL parser
Public Class URLParser
Private m_url As String

Public Sub Parse(url As String)
m_url = url
End Sub

Public Function GetProtocol() As String
Dim pos As Long
pos = InStr(m_url, "://")

If pos > 0 Then
GetProtocol = Left$(m_url, pos - 1)
Else
GetProtocol = ""
End If
End Function

Public Function GetDomain() As String
Dim startPos As Long
Dim endPos As Long

startPos = InStr(m_url, "://")
If startPos > 0 Then
startPos = startPos + 3
Else
startPos = 1
End If

endPos = InStr(startPos, m_url, "/")
If endPos > 0 Then
GetDomain = Mid$(m_url, startPos, endPos - startPos)
Else
GetDomain = Mid$(m_url, startPos)
End If
End Function

Public Function GetPath() As String
Dim startPos As Long
Dim endPos As Long

startPos = InStr(m_url, "://")
If startPos > 0 Then
startPos = InStr(startPos + 3, m_url, "/")
Else
startPos = InStr(m_url, "/")
End If

If startPos > 0 Then
endPos = InStr(startPos, m_url, "?")
If endPos > 0 Then
GetPath = Mid$(m_url, startPos, endPos - startPos)
Else
GetPath = Mid$(m_url, startPos)
End If
Else
GetPath = ""
End If
End Function

Public Function GetLastPathSegment() As String
Dim path As String
Dim pos As Long

path = GetPath()

' Remove trailing slash
If Right$(path, 1) = "/" Then
path = Left$(path, Len(path) - 1)
End If

pos = InStrRev(path, "/")
If pos > 0 Then
GetLastPathSegment = Mid$(path, pos + 1)
Else
GetLastPathSegment = path
End If
End Function
End Class

' Example 3: String truncation utility
Function TruncateWithEllipsis(text As String, maxLength As Long, _
Optional breakAtWord As Boolean = True) As String
If Len(text) <= maxLength Then
TruncateWithEllipsis = text
Exit Function
End If

If breakAtWord Then
Dim truncated As String
Dim pos As Long

truncated = Left$(text, maxLength - 3)
pos = InStrRev(truncated, " ")

If pos > 0 Then
TruncateWithEllipsis = Left$(truncated, pos - 1) & "..."
Else
TruncateWithEllipsis = truncated & "..."
End If
Else
TruncateWithEllipsis = Left$(text, maxLength - 3) & "..."
End If
End Function

' Example 4: Email domain extractor
Function GetEmailDomain(email As String) As String
Dim atPos As Long

atPos = InStrRev(email, "@")
If atPos > 0 And atPos < Len(email) Then
GetEmailDomain = Mid$(email, atPos + 1)
Else
GetEmailDomain = ""
End If
End Function

Error Handling

The InStrRev function can raise errors or return Null:

Type Mismatch (Error 13): If arguments are not string-compatible or numeric where expected
Invalid use of Null (Error 94): If stringcheck or stringmatch is Null and result is assigned to non-Variant
Invalid procedure call (Error 5): If start is 0 or negative (except -1)

On Error GoTo ErrorHandler
Dim pos As Long
Dim fileName As String

fileName = "document.txt"
pos = InStrRev(fileName, ".")

If pos > 0 Then
Debug.Print "Extension: " & Mid$(fileName, pos + 1)
Else
Debug.Print "No extension found"
End If
Exit Sub

ErrorHandler:
MsgBox "Error in InStrRev: " & Err.Description, vbCritical

Performance Considerations

Binary vs Text Compare: Binary comparison (vbBinaryCompare) is faster than text comparison
String Length: Performance degrades with very long strings
Start Position: Using specific start position can improve performance for partial searches
Reverse Search: More efficient than repeated InStr calls to find last occurrence
Alternative: For complex pattern matching, consider regular expressions (VBScript.RegExp)

Best Practices

Test for Found: Always check if result > 0 before using position
Default Start: Use -1 or omit start parameter to search from end
Path Operations: Prefer InStrRev for file path operations (finding last separator)
Extension Extraction: InStrRev is ideal for getting file extensions
Null Handling: Use Variant for result if strings might be Null
Parameter Order: Remember InStrRev has different parameter order than InStr
Return Value: Position is still counted from beginning (not from end)

Comparison with Other Functions

Function	Purpose	Search Direction	Start Parameter
`InStr`	Find substring	Left to right	Optional, before strings
`InStrRev`	Find substring	Right to left	Optional, after strings
`Like`	Pattern matching	N/A	N/A
`StrComp`	Compare strings	N/A	N/A

Platform and Version Notes

Available in VB6 and VBA (not in earlier VB versions)
Returns Long (32-bit integer), not Integer
1-based indexing (first character is position 1)
Position returned is from the start of string, not from the end
Default start is -1 (search from end)
Parameter order differs from InStr (strings first, then start)
Maximum string length is approximately 2GB

Limitations

Finds only one occurrence (the last one)
No built-in regex or wildcard support
Case-insensitive search (vbTextCompare) is slower
Different parameter order than InStr can cause confusion
Cannot search for multiple substrings in single call
Performance can degrade with very long strings

InStr: Search from beginning of string
Mid, Left, Right: Extract substrings
Replace: Find and replace text
Split: Split string into array
StrReverse: Reverse a string

InStrRev Function