When you have a large data set with text strings, you may need to find or count data based on their text content. You may know to do this with numerical values, but also know that this can be done to ant cells that contain text strings.

Think that you want to find certain names in a name list and you are not searching for the complete name but only a part of it. For example you want to count names who has “Da” in their names.

Let’s see how we can do this. We can use COUNTIF() function to do this.

The syntax of this function as follows:

= COUNTIF(range, criteria)

Range: is the cells range where you want to search for

Criteria: is the condition or the text string we are looking for

We can use =COUNTIF(A2:A20,"*Da*") to count name who has “Da” in their names.

Note that we have to use an asterisk (*) and "*" is a wildcard matching any number of characters.

You have to remember that this function is case insensitive. That means "*Da*" and "*da*" would give you the same result.

If you want to count text string with exact number of characters then use question mark (?) to represent one character. See below example:

=COUNTIF(A2:A19,"*ex?????") syntax counts text strings which has “ex” in the content and has only 5 characters after “ex”.

It has counted only two items (see the highlighted cells).

If you want to count cells containing an actual question mark or asterisk, type a tilde () before the ? or * character in the criteria. (e.g. =COUNTIF(range,"*?*")