FIND - list of strings to check a cell for

[cmhoz]

Hi All -

I have a list of 'descriptions' - 6500+ of them. I need to check each one to see if it contains any of the following words: inject, cream, ointment, drops.

Currently, I'm doing 4 separate 'find' statements to see if any of the words exist in each description. However, as the list of words to search for can/will change/expand I'm wondering if there is a more clever way to do this... a magic formula or a UDF or something where I can provide a list to search for in a range and it cycles through the description to search??

Any ideas??

Thanks!

 

[cmhoz]

This is what I came up with... seems to work. 'checkme' is a number - it essentially counts the number of search strings that appear in the description... don't know if there is a fancier/better way, but it does what I need!

_____________________________
Function CheckText(CellToCheck As Range)
Dim CheckRange, Hit, cl As Range
Dim findme As String

found = 0
checkme = CellToCheck.Text

For Each cl In Range("excludeme")

findme = cl.Text
lencheckme = Len(checkme)
substituted = WorksheetFunction.Substitute(checkme, findme, "")
lenreplaced = Len(substituted)
lenfindme = Len(findme)

found = found + ((lencheckme - lenreplaced) / lenfindme)

Next cl

CheckText = found

End Function
_____________________________



_____________

 

[Aladin Akyurek]

Hi All -

I have a list of 'descriptions' - 6500+ of them. I need to check each one to see if it contains any of the following words: inject, cream, ointment, drops.

Currently, I'm doing 4 separate 'find' statements to see if any of the words exist in each description. However, as the list of words to search for can/will change/expand I'm wondering if there is a more clever way to do this... a magic formula or a UDF or something where I can provide a list to search for in a range and it cycles through the description to search??

Any ideas??

Thanks!

Create a range housing inject, cream, ointment, and drops. Name this range KeyWords.

Assuming that the first description is in A2...

B2:

=ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH(" "&KeyWords&" "," "&A2&" "))+0


A result of 1 means that A2 contains at least one of the key words, otherwise not.