• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find a if one word of a list exists in a cell [SOLVED]

theflysurfer

New Member
Hello,


Let's say in column A I have a list of cells with a lot of text in each cell.

In column B, I have a a bunch of key words that I would like to find in column A.


For each cell of column A, i would like to test if it contains at list one of the word of the list in the column B.


Can anyone help me ? Thanks in adavance,
 

keymaster

New Member
countif and sumproduct my friend.. :)


in column C write, =SUMPRODUCT(COUNTIF(A1,$B$1:$B$10))


assumes B1:B10 has the keywords.
 

dbotella

New Member
'llo,


To find one word (or rather a string match), you can add use wildcards : =SUMPRODUCT(COUNTIF(A1,"*"&$B$1:$B$10&"*"))


Hope this helps.


David
 

theflysurfer

New Member
Hi,


Thanks for the formulas, it works great.

Do you think it is possible to find which word has been found (if a word of the list exists, it will be unique and appears only one time in a cell in column A)


Thanks,
 
Top