How to Count the Number of Cells that Contain Specific Text

Mar 18, 2020

Let's say you want to ensure that a column contains text, not numbers. Or, perhapsyou want to find all orders that correspond to a specific salesperson. If you have no concern for upper- or lowercase text, there are several ways to check if a cell contains text.

Usually with the IF function you test a cell for a complete match, but in this example we need a partial match on text.


To check if a cell contains specific text, you can use the ISNUMBER and the SEARCH function in Excel. There's no CONTAINS function in Excel.

1. To find the position of a substring in a text string, use the SEARCH function.

Search Function in Excel

Explanation: “duck” found at position 10, “donkey” found at position 1, cell A4 does not contain the word “horse” and “goat” found at position 12.

2. Add the ISNUMBER function. The ISNUMBER function returns TRUE if a cell contains a number, and FALSE if not.

IsNumber Function

Explanation: cell A2 contains the word “duck”, cell A3 contains the word “donkey”, cell A4 does not contain the word “horse” and cell A5 contains the word “goat”.

3. You can also check if a cell contains specific text, without displaying the substring. Make sure to enclose the substring in double quotation marks.

Double Quotation Marks

4. To perform a case-sensitive search, replace the SEARCH function with the FIND function.

Case-sensitive Search

Explanation: the formula in cell C3 returns FALSE now. Cell A3 does not contain the word “donkey” but contains the word “Donkey”.

5. Add the IF function. The formula below (case-insensitive) returns “Found” if a cell contains specific text, and “Not Found” if not.

Check If a Cell Contains Specific Text

6. You can also use the IF and the COUNTIF function in Excel to check if a cell contains specific text. However, the COUNTIF function is always case-insensitive.

If and Countif Function

Explanation: the formula in cell C2 reduces to =IF(COUNTIF(A2,"duck"),“Found”,“Not Found”). An asterisk (*) matches a series of zero or more characters. Visit our page about the COUNTIF function to learn all you need to know about this powerful function.

#Tutorial#How To#Functions

How to Calculate Compound Annual Growth Rate in Excel

How To Make Programming Charts In Excel