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
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
Which version of Excel?
ASKER
Excel 2003
Do you want to be able to add or subtract from your list of words without changing your formula?
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.
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:
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
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
=--(SUM(COUNTIF(B2,"*"&$K$
Thomas
Nice work nutsch!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Still as an array formula (validate with Ctrl+Shift+Enter instead of plain Enter)
ASKER
thanks, it works