Check if a string contains another string Check if a string contains another string vba vba

Check if a string contains another string


Use the Instr function

Dim pos As Integerpos = 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 IntegerposOf_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 IfEnd 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 Integerpos = 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 Integerpos = InStrRev("find the comma, in the string", "the")

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


matomo