SEARCH function for array of strings rather than single string

Question

Here's a simple one for any Excel users - I'm currently trying to write a function that will check if a cell contains at least one string out of an array of strings and return a TRUE/FALSE value. My function as it stands looks something like this:

=IF(ISNUMBER(SEARCH({"cat","dog","clog","frog","log","beaver"}, E2)), "og-word", "something else")

The issue with this is that the SEARCH function only checks for the first string in the array rather than checking for all of them. Any quick fixes? Not sure if this would work as I haven't checked yet, but I would rather not throw an OR function in there and repeat the SEARCH function multiple times, for cleanliness sake.

Thanks in advance for the responses!

#1 : http://www.mrexcel.com/forum/excel-questions/601195-check-multiple-text-strings-cell.html — sancho.s ReinstateMonicaCellio, Jul 29 '14 at 13:39
#2 : http://www.get-digital-help.com/2009/07/15/search-for-multiple-text-strings-in-multiple-cells-in-excel-part-2/ — sancho.s ReinstateMonicaCellio, Jul 29 '14 at 13:39

score 1 · Accepted Answer · edited May 23 '17 at 11:57

1

As posted in comments #1 and #2, these two work (as I tested)

=IF(SUM(COUNTIF(E2,"*"&{"cat","dog","clog","frog","log","beaver"}&"*")),"og-word","something else")

=IF(OR(ISNUMBER(SEARCH({"cat","dog","clog","frog","log","beaver"},E2))),"og-word","something else")

edited May 23 '17 at 11:57

Community

1
1

answered Jul 29 '14 at 13:56

sancho.s ReinstateMonicaCellio

14,708
20
93
185

Awesome, thanks Sancho! Could you explain why the first one works, i.e. the logic behind it? – cjacobso Jul 30 '14 at 14:54
@cjacobso - I would recommend you test yourself the result of each of the nested functions, so you can readily see the effect of each. It is quite instructive! – sancho.s ReinstateMonicaCellio Jul 31 '14 at 12:59
@cjacobso - [This question](http://stackoverflow.com/q/20078467/2707864) and its answers are likely relevant. Check in particular [this one](http://stackoverflow.com/a/20080713/2707864). – sancho.s ReinstateMonicaCellio Jul 31 '14 at 13:22

score 0 · Answer 2 · answered Jul 29 '14 at 13:43

0

This will return True if A1 contains either fat or thin:

=NOT(LEN(SUBSTITUTE(SUBSTITUTE(A1,"fat",""),"thin",""))=LEN(A1))

answered Jul 29 '14 at 13:43

Gary's Student

95,722
10
59
99

SEARCH function for array of strings rather than single string

2 Answers2