VBA: Find Text in a String (instr)

Automate Excel

VBA: Find Text in a String (instr)

You can use the instr function in VBA to test if a string contains certain text. The result is the number of times the specified text appears in the string.

The following code snippet tests the string “Look in this string” for the word “Look”. The result is 1 since the word appears once.

Public Sub FindSomeText()

MsgBox InStr("Look in this string", "Look")

End Sub

Sidenote: This test is case senstive. To skip the case sensitive check, enclose the look-in string and test string in Ucase(). Example

This returns 0 becuase the case is wrong for the word “look”

MsgBox InStr("Look in this string", "look")

However the following returns 1 becuase it turns both strings to all caps before evaluating:

MsgBox InStr(UCase("Look in this string"), UCase("look"))

A quick use of the instr in an if…then statement
Public Sub FindSomeText()

If InStr("Look in this string", "look") = 0 Then
MsgBox "woops, no match"
Else
MsgBox "at least one match"
End If

End Sub

Related posts

2 Responses

  1. anonymous Says:

    InStr() allows for a fourth [optional] argument. to our advantage setting this argument to vbTextCompare makes the string search case-insensitive

    ie,
    MsgBox InStr(UCase(”Look in this string”), UCase(”look”))
    should return the same as
    MsgBox InStr(”Look in this string”, “look”, vbTextCompare)

  2. eminent_eddie Says:

    Instr() returns the position of the first occurrence of one string within another. Not the amount of times the string is found.

    cheers.
    ed

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.