J-Walk & Associates, Inc.

New Tip: Searching For Words

I posted a new tip today, a VBA function that answers the question: Is A Particular Word Contained In A Text String?

I wrote this function to solve a practical problem. At my other blog, I've been posting lists of songs that contain a particular word. I have my music list in an Excel workbook, but none of the built-in tools are able to let me search for a particular word without getting false hits. For example, if I search for the word friend, I'll also get song titles that contains friends, friendly, friendship, and so on.

The ExactWordInString function, listed in the tip, solves this problem and lets me identify only the songs that contains the exact word I specify.

- Reader Comments -

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

1. By Toad. Comment posted 01 April, 2009 5:18pm

   Cool. Can we look forward to a CONTAINSEXACT function in the next iteration of PUP?
   
   By the way, the "J-Walk & Associates, Inc." link in the upper left corner of your page is missing the "c." at the end. At least, it doesn't appear on my machine (Windows XP with Firefox 3.0.8).
2. By John Walkenbach. Comment posted 01 April, 2009 5:42pm

   Missing C? That's weird. I'm also using 3.0.8 and here's what it looks like to me:
   
   
   
   I recently changed the CSS so hyperlinks are bold. That's probably the problem there. I'll see if I can fix it.
3. By Toad. Comment posted 01 April, 2009 6:09pm

   Here's what it looks like on my screen:
   
   
4. By John Walkenbach. Comment posted 01 April, 2009 6:52pm

   Toad, the problem could be that you have a few dirty pixels on your monitor that are blocking the font from rendering properly. Try this and let me know if it fixes it:
   
   http://www.raincitystory.com/flash/screenclean.swf
   
   If not, I should probably put a little graphic there instead of the text. But I'm still rather stumped as to why it's cutting off those two characters.
5. By Toad. Comment posted 01 April, 2009 6:56pm

   Thanks. The link worked, and it renders properly now.
6. By Dick Kusleika. Comment posted 02 April, 2009 7:12am

   How are you going to search for songs with the number 9 in them?
7. By Toad. Comment posted 02 April, 2009 11:29am

   I'll bet it's because I use a minimum font size of 14 in Firefox.
8. By chaitanya sagar. Comment posted 06 April, 2009 5:03am

   the function you have specified is very useful to search the words without false hits. especially in the official and academic work.thanks for the post.
9. By Volt. Comment posted 15 August, 2009 6:24pm

   I don't think J-Walk's and Rick Rothstein's routines will produce the same result.
   
   Try it with:
   
   Word = "O'Leary" and Text = "Mrs. O'Leary has a cow"
   or
   Work = "x-y" and Text = "x-y Axis"
10. By Rick Rothstein (MVP - Excel). Comment posted 01 November, 2009 12:24pm

    Sorry for coming to this comment section so late. Yes, Volt, you are right... my function and John's do not produce the same output for those (and, of course, other similarly constructed examples); however, given the sought after functionality embodied by this thread, I think my function performs as was intended by John when he developed his code.
11. By Rick Rothstein (MVP - Excel). Comment posted 01 November, 2009 12:40pm

    If anyone is interested in a worksheet formula version of the function, here is what I developed...
    
    =IF(ISNUMBER(SEARCH(B2,A2)),AND(NOT(ISNUMBER(FIND(UPPER(MID("="&A2;&"=",SEARCH(B2,A2),1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ"))),NOT(ISNUMBER(FIND(UPPER(MID("="&A2;&"=",SEARCH(B2,A2)+LEN(B2)+1,1)),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))))
    
    The idea for doing a worksheet formula came from a blog entry on the "Get Excel Help" blog as this link...
    
    http://www.get-digital-help.com/2009/07/25/exact-word-in-string-without-using-vba-in-excel/comment-page-1/#comment-1538
    
    I initially posted the above formula there, but figured it would make a good follow post here is this thread.