Finding

There are three built-in functions you can use for finding substrings.


INSTR([start,] string1, string2 [,compare])

You can use the INSTR function to return the position of a substring within a larger string.
This function is case sensitive.
When start is left blank, 1 is used.
When the substring cannot be found, 0 is returned.

InStr("C:\Temp\","C:\") = 1 
InStr(1,"C:\Temp\","C:\") = 1
InStr(1,"C:\Temp\","E:\") = 0
InStr(1,"C:\Temp\","Temp") = 4
InStr(1,"C:\Temp\","\") = 3

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

You can use the INSTRREV function to return the position of a substring within a larger string.
This function is case sensitive.
When start is left blank, -1 is used (meaning the last character).
When the substring cannot be found, 0 is returned.
Although this function searches from the right hand side, it returns the number of characters from the left (same as INSTR).

InStrRev("C:\Temp\","C:\") = 1 
InStrRev("C:\Temp\","C:\",-1) = 1
InStrRev("C:\Temp\","E:\",-1) = 0
InStrRev("C:\Temp\","Temp",-1) = 4
InStrRev("C:\Temp\","\",-1) = 8

MID(string, start [,length])

You can use the MID - Function to return the text which is a substring of a larger string.
Not to be confused with the MID - Statement which replaces a specified number of characters.

Mid("C:\Temp\",1) = "C:\Temp\" 
Mid("C:\Temp\",3) = "\Temp\"
Mid("C:\Temp\",3,1) = "\"

Finding the first occurrence

You can use the INSTR function to return the position of a substring within a larger string.

Dim sText As String 
sText = "The First Occurrence"
Debug.Print InStr(sText, "e") '= 3

Finding the last occurrence

You can use the INSTRREV function to return the position of a substring within a larger string.

Dim sText As String 
sText = "The Last Occurrence"
Debug.Print InStrRev(sText, "e") '= 19

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext