2

I'd like the quickest way (in excel VBA) to identify whether one string occurs anywhere within another - an 'includes' function perhaps?

  • 4
    Possible duplicate of [Check if a string contains another string](https://stackoverflow.com/questions/15585058/check-if-a-string-contains-another-string) – puzzlepiece87 Aug 22 '17 at 15:18

3 Answers3

14

I believe INSTR() is the function; if it returns anything other than zero, the string is found.

exists = InStr("avbprogram", "vb") <> 0
Carl Manaster
  • 39,912
  • 17
  • 102
  • 155
12

Carl is correct but you should also know that the default compare option for InStr is case-sensitive. If you want to do case-insensitive checks you should either wrap your arguments in LCase/UCase or use the extended form of the InStr function as shown below:

exists = InStr(1, "avbprogram", "vb", vbTextCompare)

where the first argument is the index of the first character to start comparing from and the last argument indicates case-insensitive comparison. The short-hand that Carl showed is actually equivalent to what is shown below:

exists = InStr(1, "avbprogram", "vb", vbBinaryCompare)
0

Another option is using the Like operator.

The following usage guidance from MSDN is helpful:

Dim testCheck As Boolean
' The following statement returns True (does "F" satisfy "F"?)
testCheck = "F" Like "F"
' The following statement returns False for Option Compare Binary
'    and True for Option Compare Text (does "F" satisfy "f"?)
testCheck = "F" Like "f"
' The following statement returns False (does "F" satisfy "FFF"?)
testCheck = "F" Like "FFF"
' The following statement returns True (does "aBBBa" have an "a" at the
'    beginning, an "a" at the end, and any number of characters in 
'    between?)
testCheck = "aBBBa" Like "a*a"
' The following statement returns True (does "F" occur in the set of
'    characters from "A" through "Z"?)
testCheck = "F" Like "[A-Z]"
' The following statement returns False (does "F" NOT occur in the 
'    set of characters from "A" through "Z"?)
testCheck = "F" Like "[!A-Z]"
' The following statement returns True (does "a2a" begin and end with
'    an "a" and have any single-digit number in between?)
testCheck = "a2a" Like "a#a"
' The following statement returns True (does "aM5b" begin with an "a",
'    followed by any character from the set "L" through "P", followed
'    by any single-digit number, and end with any character NOT in
'    the character set "c" through "e"?)
testCheck = "aM5b" Like "a[L-P]#[!c-e]"
' The following statement returns True (does "BAT123khg" begin with a
'    "B", followed by any single character, followed by a "T", and end
'    with zero or more characters of any type?)
testCheck = "BAT123khg" Like "B?T*"
' The following statement returns False (does "CAT123khg"?) begin with
'    a "B", followed by any single character, followed by a "T", and
'    end with zero or more characters of any type?)
testCheck = "CAT123khg" Like "B?T*"
puzzlepiece87
  • 1,537
  • 2
  • 19
  • 36