Avatar of munch007
munch007
 asked on

Excel Search for List of Words in a cell

I have a list of words that I want to search and return a value of "1" if found in the corresponding cell.
Here is an example
List of words:
filter
cap
tube

cell Description  
b2  round filter  
b3 bolt
b4 caplug
b5 cap 1.25in

I would expect it to return a 1 for cell b2 and a 1 for cell b5.

See attached file.
 searchwords.xls
Microsoft ExcelSpreadsheets

Avatar of undefined
Last Comment
munch007

8/22/2022 - Mon
Matt Coughlin

Which version of Excel?
munch007

ASKER
Excel 2003
Matt Coughlin

Do you want to be able to add or subtract from your list of words without changing your formula?
Your help has saved me hundreds of hours of internet surfing.
fblack61
munch007

ASKER
want to be able to add/remove words from the list without changing formula.  It would be ok to change the range that the list of words is in.
Matt Coughlin

I am only able to do this with a macro.

Also in your data you have an item with a description of "capscrew" this will always get marked "1" because it contains the word "cap" as listed in your list of words.

Maybe someone else can do this with a formula, but I cannot seem to figure it out.

here is the macro:

Sub Macro1()
'
' Macro1 Macro
'
n = Range("b65536").End(xlUp).Row
o = Range("k65536").End(xlUp).Row

For i = 2 To n
    r = Range("b" & i).Value
For j = 2 To o
    s = Range("K" & j).Value
    If InStr(UCase(r), UCase(s)) >= 1 Then
    Range("g" & i).Value = 1
    GoTo 10
    End If
    
Next
Range("g" & i).Value = 0
10
Next
End Sub

Open in new window

nutsch

I have this array formula, entered with Ctrl+Shift+Enter, but it currently picks up capscrew as a match

=--(SUM(COUNTIF(B2,"*"&$K$2:$K$8&"*"))>0)

Thomas
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Matt Coughlin

Nice work nutsch!
ASKER CERTIFIED SOLUTION
nutsch

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
nutsch

Still as an array formula (validate with Ctrl+Shift+Enter instead of plain Enter)
munch007

ASKER
thanks, it works
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy