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

[theflysurfer]

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]

countif and sumproduct my friend..


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


assumes B1:B10 has the keywords.

 

[dbotella]

'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]

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,