Check if a cell contains specific text

This kind of question is responded in "iWork Formulas and Functions User Guide" available to every user from the Help menu.



In column C,
I use:
=IF(ISERROR(SEARCH("rich",$B)),"missing","")

In column D, I use:
=IF(ISERROR(SEARCH("poor",$B)),"missing","")

Yvan KOENIG (VALLAURIS, France.) dimanche 23 août 2009 10:44:04

Is there a way to modify this to do a count of the cells in a range where a specific text string is found?

Something like this:

COUNTIF(ISERROR(SEARCH("http",('Sheet 1' :: I2:I101) ) ))

Not sure how to get this to work.

Hi,
this is my first question so forgive if I'm not clear enough. I need a function that looks how many times does a string appears in a group of cells. Let's say I have a matrix which has an amount of text in each cell, and I want to know how many time the word carrot is used in the whole matrix. I hope my question is understandable.

I am assuming the word "carrot" can appear multiple times within the same cell so the COUNTIF function will not work for this case.

Set up an auxiliary (second) table that is the same size as your "matrix". Each cell in this new table corresponds to a cell in your "matrix". Each of these cells will count how many times "carrot" appears in the corresponding cell of the "matrix". The formula below will count how many times the letters "carrot" appear in that exact order in a cell. In other words, "carrotstick" is also included in the count. Capitalization is not a factor. It assumes your "matrix" top left corner is cell B2 of Table 1.

Table 2 cell B2=(LEN(UPPER(Table 1 :: B2))-LEN(SUBSTITUTE(UPPER(Table 1 :: B2),UPPER($A$1),"")))/LEN($A$1)

Copy or fill that formula to the rest of the cells.

Table 2 cell A1 = carrot (the word you are looking for)

Somewhere else have a cell that sums the cells in Table 2.

The formula works by removing all instances of the word "carrot" from the string then subtracting the length of the new string from the length of the original and dividing that by the length of the word "carrot". The result is the number of times "carrot" appears in that particular string.

You could put a space before and/or after "carrot" in cell A1 to avoid counting words such as "carrotstick" but if "carrot" is the first and/or last word of the string, it will not get counted because that space will not be there in the string.

Message was edited by: Badunit

Thanks for the feebdack 😉

Yvan KOENIG (VALLAURIS, France.) dimanche 23 août 2009 15:11:52

SB,

=COUNTIF(Sheet 1 :: Table 1 :: I2:I101,"http*")

Is that what you're looking for?