Check if a string contains another string

Question

I want to find if a string contains a     comma  in it  Do we have any other option other than reading char-by-char

User · Answer

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.

User · Answer

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      amp  Err Description  amp       vbCritical   Database Error  End End Function

User · Answer

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

User · Answer

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  quot bc quot    quot abc d quot    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  quot  ISNUMBER FIND  quot   amp  Chr  34   amp  substring  amp  Chr  34   amp   quot    quot   amp  Chr  34   amp  testString  amp  Chr  34   amp   quot    quot    End Function

User · Answer

Use the Instr function  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

[string] Check if a string contains another string

Examples related to string

Examples related to vba