CODEWITHSUNDEEP
excel
114
114

Reputation: 926

A better way to check whether multiple strings occur in a given cell

In the past I have used =IF(OR(ISNUMBER(SEARCH("*orange*",A1),ISNUMBER(SEARCH("*apple*",A1)),1,0)) to check whether, in this case, orange or apple occurs anywhere in the cell A1. If I wanted to check whether more strings were present I would need to keep adding more ISNUMBER(SEARCH()) lines. For a large number of strings this is not feasible. Is there a better way to do this without using VBA that I might be missing?

Upvotes: 1

Views: 1663

Answers (1)

Jerry
Jerry

Reputation: 71598

You can use this formula (note, you don't need to use the asterisk wildcard with SEARCH):

=SUMPRODUCT(--ISNUMBER(SEARCH({"orange","apple"},A1)))>0

This will give you TRUE if any of those two words are in A1. You can wrap it in an IF if you want a custom result message or use MIN to get 1 and 0:

=MIN(SUMPRODUCT(--ISNUMBER(SEARCH({"orange","apple"},A1))),1)

The formula works like this:

SEARCH({"orange","apple"},A1) looks for the words in A1 and returns an array of values if found, or error otherwise.

ISNUMBER checks each of these results in the array.

The two -- at the front convert the TRUE and FALSE into 1 and 0 respectively.

SUMPRODUCT adds up those 1 and 0. If it is at least 1, you'll get a final result above 0.

Upvotes: 3

Related Questions