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?
This would work to tell you the number of times the specified text appears in a string:
Function textFreq(word, findText, Optional start = 1)
If InStr(start, word, findText) > 0 Then
textFreq = 1 + textFreq(word, findText, InStr(start, word, findText) + Len(findText) + 1)
Else
textFreq = 0
End If
End Function
Also works for me:
numOfMatches = (Len(UCase(TempString)) – Len(Replace(UCase(TempString), UCase(findText), “”))) / Len(UCase(findText))
There is no such build-in function. we have to write the function on our own.
like Prinny said:
Instr(“abcbcabf”, “f”) ‘Returns 8
is there a way to extract say 5 characters from the first occurance of “c”??
Yes there is AB.
totalLength = len(theString)
someVar = instr(theString,”c”)
theResult = right(theString,totalLength-someVar)
Basically get the total length of the string
Then find the first occurence of “c” and store it as a number
then take the Right() of the total length minus the first occurence.
So if your word is abcdefghijk then
total length is 11
“c” occurs at 3
so we take 8 characters from the right and get
“defghijk”
I am new to VBA in Excel and I need to search for a word and then based on the result I want to add a character to the end on the following word.
for example:
look in “had happen”, the condition is :”if there is had, then add “ed” to “happen”
I would appreciate your help
Unfortunately this is the page with wrong information (check 2nd and 3rd comments from top) and this pages comes on top of google results.
Hi,
I want to search text which has variable length:
Ex: SR or S/R or S?R
Is there any thing like wildcard which we can use with Instr function?
Please advice
Hi, thanks for all your posts. I am using INSTR() and it works fine, but for 50k rows, takes too much time. Can you recommend me any other function that can do the same but faster?
Thanks in advance for your help.
Omar.
Omar,
Are you using a recordset object or a query? A recordset object has to go one line at a time and could take some time however if you create a query with an equation field Expr1: Instr(1, [FieldName], [String]) then set criteria > 0 this query would only return records where the String is found. Should be much faster than the recordset object. If it is still slow try indexing the field you are doing the search on. It will increase the size of your database but is worth the saved time.
Of course this is an Access solution. There is no faster option is Excel then looping one line at a time.
So this might not be the right place to publish this, but I’ve never posted on a forum before, so I thought I’d give it a shot.
I’m currently interning with a company who has hired me to help automate office processes centered around databases. So far, I’ve done things with access, excel, and VBA that I didn’t even think was possible, but for some reason, I’ve now hit a brick wall.
The task seems deceptively simple: We are importing fields from a project management database into access. Sometimes, fields such as project description or project title will have records that come in with spelling errors. Instead of meticulously reading these, or copying and pasting them into microsoft word one record at a time, what we would like to do is be able to cycle through the records belonging to a particular field and spellcheck them while they’re in the table and then highlight the misspelled word in some way.
Right now, I have a solution that will only check one word in the field, which is not sufficient. Any advice whatsoever would be appreciated. Thanks in advance.
Instr returns the position at which the search string occours – not the number of times it is contained!
I am betting that reading the range into a variant array and using the instr() on the elements would happen pretty quickly.
Hi Iam look for an solution:
I’ve got a string value like this one:
strExample = abc-cde-12
I want to extract the last number, just if there are two of the “-” in the string.
I dont know how to handle that with “InStr”.
Maybe some has a hint for me?