Search function with list of text values fails

Question

check this answer out: http://stackoverflow.com/questions/14803944/excel-searching-for-multiple-terms-in-a-cell — krib, Apr 20 '17 at 09:39

score 0 · Accepted Answer · answered Apr 20 '17 at 09:41

Using =SEARCH({"N.","No.","#"},D5) formula when you will see how the formula calculates the result using Evaluate Formula, you'll notice

evaluates to

That means formula is searching only for "N."

Therefore to search for the existence of "N.","No.","#" in a cell, number of approaches are available like:

1. =IF(COUNT(SEARCH({"N.","No.","#"},D5)),1,"")

This formula will give 1 if any of the string in the cell exists.

2. =SUMPRODUCT(--ISNUMBER(SEARCH(find_text,D5)))>0

This formula will give TRUE if any of the three string exists else FASLE.

1 Answers1