EXCEL-SEARCH wildly complex formulas - Microsoft Community

hello,

i trying make formula process huge amount of data filtering words. far have searchb commands searching cells contain; word, word , word, word or word, word not word.

i want excel write "1" if finds word in cell, , notheing "" when doesn't. when searchb 1 word problem finds word roots too, , writes "1" words contain word searched. eg;

                         concatenate(if(iserror(searchb(a$1;dd$5;1);"";if(iserror(searchb(a$1;dd$5;1));"1";""))

                         a1: cell have word; in case "hert"

                         dd5: cell searching it

well, when search "hert" "lachertje". problem if search "hert " (with space behind) loses cells have "hert" @ end of sentence "hert." or ones writen quotation marks "hert"". "

is there way searches "hert" + simbol not letter? 

that leave formula without concatenate (much simpler)

thank you

hi,

take few example , show expected result.

Office / Excel / Windows 10 / Unknown/other