Search for words in a cell, from a list. Pull those words that are there out and list them

Hi Guys

Been thinking for a while on how to pull this off... I need a formula that would search a column of descriptions from a big list of keywords and phrases. And if some of those words are found, then they are pulled from said description to be listed as a string of words set out with comers instead of spaces. But ignoring the words that are not found.

So far i have tried to use both of these formulas:

=IF(SEARCH("WORD1",$A$1:$A$10),"WORD1") &","&IF(SEARCH("WORD2",$A$1:$A$10),"WORD2")&","&IF(ISERR(SEARCH("WORD3",$A$1:$A$9)),"WORD3") Etc....

Using this formula it finds 'WORD1' and 'WORD3' (which are in the description) but not 'WORD2' ending up with having the result of:

#VALUE!


Or using this formula:

=IF(ISERR(SEARCH("WORD1",$A$1:$A$9)),"WORD1") &","&IF(ISERR(SEARCH("WORD2",$A$1:$A$9)),"WORD2") &","&IF(ISERR(SEARCH("WORD3",$A$1:$A$9)),"WORD3") Etc....

This formula gets me closer to what i want as 'WORD1' and 'WORD3' are in the description and 'WORD2' isnt. But this results in:

'FALSE,FALSE,WORD2'

Which is the reverse of what i want. What i would like would be the outcome to be 'WORD1,FALSE' and then just delete all 'FALSE' words.


Hope that someone get help me out!

Thanks.

Thanks David,

This does help a little but i was trying to get the outcome list, WORD1,WORD2,WORD3, to just be contained to each row. Where each row has its own unique list.

So for instance, if A1, A2 and A3 contained the sentences:

A1 - The cat sat on the mat
A2 - The dog sat on the stairs
A3 - The mouse was hiding in its hole

Then the list for them could read in column B:

B1 - cat,sat,mat
B2 - dog,sat,stairs
B3 - mouse,hiding,hole

Is there a formula that would do this so i can just drag it down and it fills everything in automatically?

Thanks again
Folay