VBA: Find Text in a String (instr)

August 14th, 2004 | Categories: VBA | Tags: , , ,

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

Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. anonymous
    January 10th, 2006 at 13:26
    Reply | Quote | #1

    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
    July 31st, 2008 at 08:42
    Reply | Quote | #2

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

    cheers.
    ed

  3. David
    July 1st, 2009 at 15:31
    Reply | Quote | #3

    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?

  4. Eric
    July 15th, 2009 at 19:51
    Reply | Quote | #4

    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.

  5. dajomu
    February 17th, 2010 at 10:10
    Reply | Quote | #5

    MsgBox Inst(“abc”,”a”) ‘returns 1
    but will
    MsgBox Inst(“abc”,”b”) also return 1?

    • Richard
      March 2nd, 2010 at 14:00
      Reply | Quote | #6

      No. MsgBox Instr(“abc”, “b”) returns 2.

  6. Prinny
    March 4th, 2010 at 00:43
    Reply | Quote | #7

    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”)

  7. Nev
    March 6th, 2010 at 14:17
    Reply | Quote | #8

    good test dajomu,
    the author clearly needs to correct his tutorial

    abc,a returns 1
    abc,b returns 2
    abc,c, returns 3

  8. Rich
    April 19th, 2010 at 14:39
    Reply | Quote | #9

    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!

    • April 24th, 2010 at 22:46

      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.

  9. Marc
    May 5th, 2010 at 12:21

    Is there a function that can tell you the number of times the specified text appears in a string?

    • Mike
      September 28th, 2010 at 15:26

      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

    • Anton
      July 12th, 2011 at 19:37

      Also works for me:

      numOfMatches = (Len(UCase(TempString)) – Len(Replace(UCase(TempString), UCase(findText), “”))) / Len(UCase(findText))

  10. Gopinath
    August 26th, 2010 at 09:15

    There is no such build-in function. we have to write the function on our own.

  11. AB
    October 21st, 2010 at 12:44

    like Prinny said:

    Instr(“abcbcabf”, “f”) ‘Returns 8

    is there a way to extract say 5 characters from the first occurance of “c”??

  12. Joe
    November 19th, 2010 at 14:03

    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”

  13. mon
    January 1st, 2011 at 19:33

    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

  14. February 7th, 2011 at 14:15

    Unfortunately this is the page with wrong information (check 2nd and 3rd comments from top) and this pages comes on top of google results.

  15. Ram
    February 16th, 2011 at 02:40

    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

  16. Omar
    March 28th, 2011 at 21:28

    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.

  17. Jerimiah
    May 5th, 2011 at 23:23

    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.

  18. Jerimiah
    May 5th, 2011 at 23:25

    Of course this is an Access solution. There is no faster option is Excel then looping one line at a time.

  19. Sherry
    June 10th, 2011 at 13:03

    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.

  20. Walter Rauschenberger
    June 15th, 2011 at 14:47

    Instr returns the position at which the search string occours – not the number of times it is contained!

  21. jwk
    June 21st, 2011 at 19:06

    I am betting that reading the range into a variant array and using the instr() on the elements would happen pretty quickly.

  22. Julian
    November 4th, 2011 at 12:02

    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?