Check if a string contains another string

I want to find if a string contains a ","(comma) in it. Do we have any other option other than reading char-by-char?

5 Answers

Use the Instr function (old version of MSDN doc found here)

Dim pos As Integer pos = InStr("find the comma, in the string", ",") 

will return 15 in pos

If not found it will return 0

If you need to find the comma with an excel formula you can use the =FIND(",";A1) function.

Notice that if you want to use Instr to find the position of a string case-insensitive use the third parameter of Instr and give it the const vbTextCompare (or just 1 for die-hards).

Dim posOf_A As Integer posOf_A = InStr(1, "find the comma, in the string", "A", vbTextCompare) 

will give you a value of 14.

Note that you have to specify the start position in this case as stated in the specification I linked: The start argument is required if compare is specified.

You can also use the special word like:

Public Sub Search() If "My Big String with, in the middle" Like "*,*" Then Debug.Print ("Found ','") End If End Sub 

There is also the InStrRev function which does the same type of thing, but starts searching from the end of the text to the beginning.

Per @rene's answer...

Dim pos As Integer pos = InStrRev("find the comma, in the string", ",") 

...would still return 15 to pos, but if the string has more than one of the search string, like the word "the", then:

Dim pos As Integer pos = InStrRev("find the comma, in the string", "the") 

...would return 20 to pos, instead of 6.

Building on Rene's answer, you could also write a function that returned either TRUE if the substring was present, or FALSE if it wasn't:

Public Function Contains(strBaseString As String, strSearchTerm As String) As Boolean 'Purpose: Returns TRUE if one string exists within another On Error GoTo ErrorMessage Contains = InStr(strBaseString, strSearchTerm) Exit Function ErrorMessage: MsgBox "The database has generated an error. Please contact the database administrator, quoting the following error message: '" & Err.Description & "'", vbCritical, "Database Error" End End Function 

You wouldn't really want to do this given the existing Instr/InstrRev functions but there are times when it is handy to use EVALUATE to return the result of Excel worksheet functions within VBA

Option Explicit Public Sub test() Debug.Print ContainsSubString("bc", "abc,d") End Sub Public Function ContainsSubString(ByVal substring As String, ByVal testString As String) As Boolean 'substring = string to test for; testString = string to search ContainsSubString = Evaluate("=ISNUMBER(FIND(" & Chr$(34) & substring & Chr$(34) & ", " & Chr$(34) & testString & Chr$(34) & "))") End Function