ebmgh.com

I found myself wishing that Excel or VBA had a simple way to test whether a string exists in another string. In Python you can type

if substring in string:

  #Then do something…

But Excel lacks this convenience. You can sort of hack the built in InStr function to do it for you. Since I always found this confusing (who can remember what the difference is between “text compare” and “binary compare”?), I created a simple function called Contains. On the spreadsheet, you can just type

=Contains("abcdefg", "a"

) and it returns TRUE. In VBA, it is useful in IF statements. Not as clean as Python, but a helpful convenience.

Function Contains(str As String, substr As String, _

   Optional CaseSensitive As Boolean = False) As Boolean

 

'Does a simple test to see if str contains substr

'Example: =Contains("abcd","a") returns TRUE

'         =Contains("abcd","x") returns FALSE

 

'Set CaseSensitive flag to TRUE if you don't want "a" to find "A"

'         =Contains("ABCD","a",True) returns FALSE

'         =Contains("ABCD","a",False) returns TRUE (because the comparison is case sensitive)

 

Dim opt As Long

 

If CaseSensitive Then

  opt = vbBinaryCompare

Else

  opt = vbTextCompare

End If

 

If InStr(1, str, substr, opt) > 0 Then

  Contains = True

Else

  Contains = False

End If

 

End Function