J-Walk & Associates, Inc.

Improving A Function

Today I was humbled by a fellow MVP. Rick Rothstein found my ExactWordInString VBA function, and sent me a much simpler function that accomplishes the same thing. My function took 12 statements; his takes one statement.

I updated my post with his improved version: Is A Particular Word Contained In A Text String?

- Reader Comments -

Following are comments in response to this item.
The most recent comment is at the bottom.

1. By Sam. Comment posted 23 July, 2009 8:10pm

   Well there is a still simpler solution
   
   If you want to return just TRUE or FALSE
   
   Function CountStr(OrgText As Variant, SearchText As Variant)
   CountStr = UBound(Split(OrgText, SearchText)) > 0
   End Function
   
   
   If you want to return number of instances of a string
   
   Function CountStr(OrgText As Variant, SearchText As Variant)
   CountStr = UBound(Split(OrgText, SearchText))
   End Function
2. By John Walkenbach. Comment posted 23 July, 2009 9:26pm

   Read it again, Sam.
3. By Sam. Comment posted 24 July, 2009 1:12am

   John....Sorry I read the post in a hurry....Banging my head on the Wall
4. By John Walkenbach. Comment posted 24 July, 2009 6:40am

   Too bad it wasn't an audio post. Then I could have said, "Play it again, Sam."
5. By Hui.... Comment posted 26 July, 2009 6:32am

   John, Your function works correctly if looking up numbers within a text string, Ricks doesn't
6. By Brett. Comment posted 28 July, 2009 9:16am

   This seems like a topic in desparate need of Regular Expressions. My quick attempt yeilds a 4 line solution:
   

   Function FoundWord(str As String, word As String) As Boolean
    Set Regex = CreateObject("vbscript.regexp")

    Regex.IgnoreCase = False
    Regex.Pattern = "\b" & word & "\b"

    FoundWord = Regex.Execute(str).Count
End Function 

   
   
   Typical regular expression usage in VBA takes many more declarations and such than this, but after stripping it down to the minimum that's what I get.
   
   The "\b" means edge of a word which means any punctuation, whitespace, or edge of a line. This unfortunately means that FoundWord("Brett's comment","Brett") and FoundWord("merry-go-round","merry") return true. This could easily be accounted for by some other greater RegEx expert than myself.
   
   This can also return True or False on phrases. FoundWord("This includes a phrase!","a phrase") returns True.
7. By fzz. Comment posted 24 August, 2009 7:19pm

   OK, why is any udf needed when assuming space separators this could be done using formulas like
   
   =ISNUMBER(FIND(" "&WordSought;&" "," "&TextSearched;&" "))
   
   or getting as flexible as the regular expression and Like approaches,
   
   =COUNT(SEARCH(MID(TextSearched,FIND(WordSought,TextSearched)+{0,1}*LEN(WordSought)-{1,0},1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=0
   
   I understand simplicity is a goal, so a generic regex search function would be the best approach.
8. By fzz. Comment posted 25 August, 2009 12:19pm

   Let me add, a regex search function as in the OpenOffice Calc formula
   
   =ISNUMBER(SEARCH("\<"&WordSought;&"\>";TextSearched))
   
   When, oh when, will Excel catch up to OpenOffice Calc?
9. By Rick Rothstein. Comment posted 07 October, 2009 7:51am

   Question to Hui...
   
   You say that John's function "works correctly if looking up numbers within a text string" and that mine doesn't... can you give me an example? I tried looking up "12" in "ab 12 cd" and looking up "12AB" in "WX 12AB YZ" and John's function returns False for both.
10. By Rick Rothstein. Comment posted 07 October, 2009 2:25pm

    To fzz...
    
    Your "getting as flexible as the regular expression and Like approaches", namely...
    
    =COUNT(SEARCH(MID(TextSearched,FIND(WordSought,TextSearched)+{0,1}*LEN(WordSought)-{1,0},1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))=0
    
    doesn't appear to work correctly. If the TextSearched is "The trick is", then if the WordSought is "rick", the formula returns FALSE (as it should); however, if the WordSought is "Rick" (capitalizing the first letter, actually, any or all letters), then your formula returns TRUE.
    
    To answer your other question, "why is any udf needed?"... my answer would be so the function can be used either in a worksheet formula or within one's own VB code... John and my functions work in either situation.
11. By James Jordin. Comment posted 20 October, 2009 8:03am

    John and Rick - these functions are excellent, and exactly what I've been trying to achieve recently... the only caveat being: I'd like to be able to count instances.
    
    Any hints on whether this function could be nested with a COUNT function, and if so, how?
    
    Many thanks in advance!
12. By Bob Phillips. Comment posted 20 October, 2009 12:39pm

    How about this
    
    Function NumInstancesInString(Text As String, Word As String) As Long
    NumInstancesInString = (Len(" " & Text & " ") - Len(Replace(" " & Text & " ", " " & Word & " ", ""))) / (Len(Word) + 2)
    End Function
13. By Rick Rothstein. Comment posted 20 October, 2009 9:28pm

    To James Jordin: Try this function...
    
    Function CountExactWords(Text As String, Word As String) As Long
    Dim X As Long, WordLen As Long
    WordLen = Len(Word)
    For X = 1 To Len(Text) - WordLen + 1
    If UCase(Mid(" " & Text & " ", X, WordLen + 2)) Like _
    "[!A-Z]" & UCase(Word) & "[!A-Z]" Then
    CountExactWords = CountExactWords + 1
    End If
    Next
    End Function
    
    Rick
14. By Rick Rothstein. Comment posted 20 October, 2009 9:33pm

    To Bob Phillips: I'm afraid that won't work... it won't count, as but one example, the word next to a punctuation mark such as at the end of a sentence or next to an opening or closing parenthesis. The spaces I concatenated on either side of the word were to simply provide a non-alpha character for the Like pattern to match... any non-alpha character could have been used in place of the two space characters.
15. By Rick Rothstein. Comment posted 21 October, 2009 7:46am

    More as a test than anything else, I am just reposting the CountExactWords function I posted for James Jordin with "code formatting" tags to try and make it look better and be easier to copy/paste into your code procedures...
    
    

    Function CountExactWords(Text As String, Word As String) As Long
  Dim X As Long, WordLen As Long
  WordLen = Len(Word)
  For X = 1 To Len(Text) - WordLen + 1
    If UCase(Mid(" " & Text & " ", X, WordLen + 2)) Like _
                  "[!A-Z]" & UCase(Word) & "[!A-Z]" Then
      CountExactWords = CountExactWords + 1
    End If
  Next
End Function