Results 1 to 8 of 8

Thread: InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    InStrExact - Find Location Of A Word, As A Word, Not Embedded Within Another Word

    .
    *CORRECTED CODE AS OF JULY 31, 2014 (See Message #6 For Details) ****


    The InStr function is a handy tool. It will return the character position of text that is embedded within a larger text string. And while this is handy, the function is useless for finding words as stand-alone words in the text. Let's say you had this text string...

    TextString = "Don't tell me, but your name is Don, right?"

    and you wanted to find the character position of the name Don in that text. If you used the InStr function like this...

    MsgBox InStr(TextString, "Don")

    It would return the number 1 because it would find the letters "Don" inside the word "Don't" and never look further into the text to see if Don existed as a stand-alone word or not. Even using InStr's optional arguments and having it perform a binary search would not help because the letter casing for the first 3 characters of "Don't" is the same a for the name "Don". Here is a function which can be called from your VB code or which can be used as a UDF on a worksheet, if desired, that will find text as stand-alone words that are not embedded as part of other words...

    Code:
    Function InStrExact(Start As Long, SourceText As String, WordToFind As String, _
                        Optional CaseSensitive As Boolean = False, _
                        Optional AllowAccentedCharacters As Boolean = False) As Long
      Dim x As Long, Str1 As String, Str2 As String, Pattern As String
      Const UpperAccentsOnly As String = "ÇÉÑ"
      Const UpperAndLowerAccents As String = "ÇÉÑçéñ"
      If CaseSensitive Then
        Str1 = SourceText
        Str2 = WordToFind
        Pattern = "[!A-Za-z0-9]"
        If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAndLowerAccents)
      Else
        Str1 = UCase(SourceText)
        Str2 = UCase(WordToFind)
        Pattern = "[!A-Z0-9]"
        If AllowAccentedCharacters Then Pattern = Replace(Pattern, "!", "!" & UpperAccentsOnly)
      End If
      For x = Start To Len(Str1) - Len(Str2) + 1
        If Mid(" " & Str1 & " ", x, Len(Str2) + 2) Like Pattern & Str2 & Pattern _
           And Not Mid(Str1, x) Like Str2 & "'[" & Mid(Pattern, 3) & "*" Then
          InStrExact = x
          Exit Function
        End If
      Next
    End Function


    This function takes three required arguments and provides for two additional optional arguments. The first argument, named Start, is required and lets you set the starting character number for the search to begin at (use 1 to start the search at the first character). The second argument, named SourceText, is required and is the text to look for the word in. The third argument, named WordToFind, is required and, as the name indicates, is the word whose position you want to locate. The fourth argument, named CaseSensitive, is optional and allows you to make the search case sensitive or not (the default is FALSE representing a case insensitive search). The fifth argument, named AllowAccentedCharacters, is optional and allows certain accented letters to be considered as being part of a word (the default is False meaning only normal ASCII characters can make up the word). For example, with the default value of False, the word "resumé" would never be found because the accented "e" (é) would be considered a non-letter. Setting the fifth argument to True would allow the function to find the word "resumé". I only allowed for three accented letters "ç", "é" and "ñ" (in both lower and upper case), but you can add more if you wish by modifying the UpperAccentsOnly and UpperAndLowerAccents constants (the Const statements at the beginning of the code).


    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use InStrExact just like it was a built-in Excel function. For example,

    =InStrExact(1,A1,B1)
    Last edited by Rick Rothstein; 07-28-2016 at 11:04 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Last edited by DocAElstein; 04-07-2024 at 12:52 PM.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Admin View Post
    There is an extra F (FFunction) in the beginning.
    Hmm, I wonder how that got in there, especially given that I copied the function from my working test code and pasted it into the new thread window when I first created it. Anyway, thanks for noticing it... I just edited the original thread message to remove the extra "F".

    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=jTmVtPHtiTg&lc=Ugy_RiNN_kAqUvZ8W994AaABAg
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNsaS3Lp1
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgR1EPUkhw
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNe_XC-jK
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgNPOdiDuv
    https://www.youtube.com/watch?v=QjEWAJ3d-jw&lc=UgxJLVpwY8fIla7G-pN4AaABAg.9BLeCWVhxdG9wgN7AC7wAc
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=DVFFApHzYVk&lc=Ugyi578yhj9zShmhuPl4AaABAg
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgxvxlnuTRWiV6MUZB14AaABAg
    https://www.youtube.com/watch?v=_8i1fVEi5WY&lc=Ugz0ptwE5J-2CpX4Lzh4AaABAg
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxoHAw8RwR7VmyVBUt4AaABAg. 9C-br0lEl8V9xI0_6pCaR9
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=Ugz5DDCMqmHLeEjUU8t4AaABAg. 9bl7m03Onql9xI-ar3Z0ME
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgxYnpd9leriPmc8rPd4AaABAg. 9gdrYDocLIm9xI-2ZpVF-q
    https://www.youtube.com/watch?v=0ltJS7uHfK4&lc=UgyjoPLjNeIAOMVH_u94AaABAg. 9id_Q3FO8Lp9xHyeYSuv1I
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-30-2023 at 02:56 PM.

  4. #4
    Senior Member
    Join Date
    Jun 2012
    Posts
    337
    Rep Power
    13
    Hi Rick,

    Nice function !

    I'd prefer not to restrict it to 26 characters; 2 alternatives ?

    Code:
    Sub snb()
        MsgBox find_snb("Dön", "Dön't tell me, but your name is Dön, right?", 2)
    End Sub
    
    Sub snb2()
        MsgBox find_snb2("don", "Don't tell me, but your name is Don, right?")
    End Sub
    
    Function find_snb(c00, c01, x)
       If x = 1 Then find_snb = InStr(" " & Replace(Replace(Replace(Replace(Replace(c01, ",", " "), ".", " "), ";", " "), "?", " "), "!", " ") & " ", " " & Trim(c00) & " ")
       If x = 2 Then find_snb = InStr(" " & Replace(Replace(Replace(Replace(Replace(LCase(c01), ",", " "), ".", " "), ";", " "), "?", " "), "!", " ") & " ", " " & Trim(LCase(c00)) & " ")
    End Function
    
    Function find_snb2(c00, c01, x)
       find_snb2 = "word number " & Application.Match(c00, Split(Application.Trim(Replace(Replace(Replace(Replace(Replace(c01, ",", " "), ".", " "), ";", " "), "?", " "), "!", " "))), 0)
    End Function

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by snb View Post
    I'd prefer not to restrict it to 26 characters; 2 alternatives ?
    Well, theoretically I don't limit it to 3 (not 2) "alternatives"... I stated this at the end of my article...

    "I only allowed for three accented letters "ç", "é" and "ñ" (in both lower and upper case),
    but you can add more if you wish by modifying the UpperAccentsOnly and UpperAndLowerAccents
    constants (the Const statements at the beginning of the code)."

    This allows the programmer the ability to change the function to meet their own personal needs, either permanently or temporarily, as the particular situation may require. The programmer is also free to turn the constant into a variable and modify the accented letters' list "on the fly" within the same application if the need arises to cater to such variability.

  6. #6
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    I posted this article two-and-quarter years ago and, until now, no one noticed that my code does not work correctlyy for the example I give in the beginning description!??!! Someone in another forum posted that all he could get InStrExact to return when looking for "Don" in "Don't tell me, but your name is Don, right?" is the number 1, not 33. And when I tried it, that is what I got as well. Now I can almost understand my missing this... almost, but not one of the 3,148 people who have viewed the article as of today noticed it until now? Anyway, I have posted corrected code in Message #1, so if you are currently using the original function, please replace it with what is now posted there.

  7. #7
    Junior Member
    Join Date
    Dec 2012
    Posts
    12
    Rep Power
    0
    Quote Originally Posted by Rick Rothstein View Post
    I posted this article two-and-quarter years ago and, until now, no one noticed that my code does not work correctlyy for the example I give in the beginning description!??!! Someone in another forum posted that all he could get InStrExact to return when looking for "Don" in "Don't tell me, but your name is Don, right?" is the number 1, not 33. And when I tried it, that is what I got as well. Now I can almost understand my missing this... almost, but not one of the 3,148 people who have viewed the article as of today noticed it until now? Anyway, I have posted corrected code in Message #1, so if you are currently using the original function, please replace it with what is now posted there.
    Hi Mr. Rick
    I tested your great UDF function and noticed that case sensitive doesn't work properly
    Code:
    Sub TestRun()
        MsgBox InStrExact(1, "Don't tell me, but your name is Don, right?", "Don", 1)
    End Sub
    The optional argument case sensitive .. I tried to put 0 and 1 .. I got the same result 33
    Is there any thing wrong about it?

  8. #8
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by KingTamo View Post
    Hi Mr. Rick
    I tested your great UDF function and noticed that case sensitive doesn't work properly
    Code:
    Sub TestRun()
        MsgBox InStrExact(1, "Don't tell me, but your name is Don, right?", "Don", 1)
    End Sub
    The optional argument case sensitive .. I tried to put 0 and 1 .. I got the same result 33
    Is there any thing wrong about it?
    Since the word you are searching for has the same letter casing as the word in the text, a case sensitive and case insensitive search will always return the same value. Try changing the red highlighted word to "don" instead of "Don" and then try 0 and 1 for the 4th argument to see the difference

Similar Threads

  1. Loop through a folder and find word
    By k0st4din in forum Excel Help
    Replies: 7
    Last Post: 12-08-2012, 02:22 PM
  2. Replies: 1
    Last Post: 10-16-2012, 01:53 PM
  3. Embed ms project in a table in ms word
    By hometech in forum Word Help
    Replies: 6
    Last Post: 09-27-2012, 12:57 PM
  4. How to extract all text from a word file?
    By vsrawat in forum Word Help
    Replies: 3
    Last Post: 09-25-2012, 10:24 PM
  5. Replies: 2
    Last Post: 09-25-2012, 01:30 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •