CODEWITHSUNDEEP
excel
LeythG
LeythG

Reputation: 191

How do count the number of cells that contain a substring in a given array?

I have hundreds of cells that contain sentences in them. I want to be able to search each one to see if it matches something that I have provided it in an array.

For example

Column A "The dog runs fast" "The cat runs fast" "The human runs fast" "The dog is a human"

How can I search through each of the 4 cells above to check to see if the cell contains either "dog" or "human." The answer should be 3 since in the 4th cell dog and human should only count once.

Thanks ahead. I can do it in SQL, but not in excel.

Upvotes: 2

Views: 58

Answers (1)

jbaums
jbaums

Reputation: 27388

One way is to enter the following formula in B2 (assuming header row exists), and fill down column B:

=SUM(IFERROR(FIND("dog",A2),0),IFERROR(FIND("human",A2),0)) > 0

And then =COUNTIF(B:B, TRUE).

Upvotes: 1

Related Questions