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
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)
Instr() returns the position of the first occurrence of one string within another. Not the amount of times the string is found.
cheers.
ed
How come two corrections to the example were noted (years old) and no change was made to the page? In one case, the description of the function in question was plain wrong (# of time found vs. position within string), and within the other, it noted that the extended example wasn’t needed since the function already did the desired test if you simply used an optional parameter. Is no one actually reading the reader responses?
Instr() will return an int of the position of the result and will return 0 when it does not exist, e.g.
MsgBox Inst(“abc”,”a”) ‘returns 1
MsgBox Inst(“abc”,”x”) ‘returns 0
Therefore the if statement is still valid.
MsgBox Inst(“abc”,”a”) ‘returns 1
but will
MsgBox Inst(“abc”,”b”) also return 1?
No. MsgBox Instr(“abc”, “b”) returns 2.
inst() returns the first position in the string where the string matches.
Instr(“abc”,”b”) ‘Returns 2, b is the second letter.
Instr(“abcbcab”, “b”) ‘Returns 2
Instr(“abcbcabf”, “f”) ‘Returns 8
When no value is found it returns 0
If you use multiple characters aka “String”, it will tell where the first position begins.
Ie. Instr(“12345678901234567890″, “456″) ‘returns 4
If you are lookin for every instance? you will need be make loops to keep checking until 0 is found.
Instr(“starting # position”, “original text”, “search text”)
good test dajomu,
the author clearly needs to correct his tutorial
abc,a returns 1
abc,b returns 2
abc,c, returns 3
I think you’re missing the point. As it says at the top, the point of the code is to indicate the existence of some text within another string. If the value returned is greater than 0 (in this example indicated by not being equal to zero) then you know that the searched value exists within the searched string. The author makes no reference to the position of the searched text as it is not relevant for this example.
I used it for this purpose and it saved me a lot of hassle so thanks!
Actually, if you read what the author says – “The result is the number of times the specified text appears in the string.” – you would see he is blatently wrong Rich. You can use it for testing if a string is present, but not for the number of times it is present.
Is there a function that can tell you the number of times the specified text appears in a string?