How to Count Cells That Contain Text in Excel

Being able to count values in Excel is an essential skill. In many cases, for example, you’ll need to count how many sales happened over a period of time. Or perhaps, you need to know how often a specific user logged into an application. Thankfully, Excel provides a ton of different ways in which to count cells that contain text, whether or not that’s any text, specific text, or partial text. In this tutorial, you’ll learn how to count cells that contain text in Excel.

For most of the use cases in this tutorial, we’ll use the Excel COUNTIF() function. Check out our in-depth guide on the function here.

By the end of this tutorial, you’ll have learned the following:

• How to count cells that contain text in Excel, including if cells contain any text at all
• How to count cells containing partial or exact strings of text
• How to count cells starting with or ending with a string of text

Count Cells That Contain Specific Text in Excel

The COUNTIF() function lets you pass in criteria to use to count cells in Excel. If you’re looking for an exact string of text in a cell, the function is perfect for this. Say you’re searching for the text “thatexcelsite”, you can use the formula =COUNTIF(A2:A10, "thatexcelsite"). This will search in the range of A2 to A10 for the specific string.

Let’s take a look at an example of how we can do this in Excel:

In the image above, we used the formula =COUNTIF(A2:A10, "thatexcelsite") to count how often that exact string exists in the range of data. Keep in mind that the COUNTIF() function is case insensitive. This means that we could’ve searched for ThatExcelSite and it would have worked in the same way. While this may lead to some unexpected results, it can also save you significant amounts of time.

Count Cells That Contain Partial Text in Excel

If you’re looking for a partial string of text in a cell, the function is perfect for this. Say you’re searching for the word “excel”, you can use the formula =COUNTIF(A2:A10, "*excel*"). This will search in the range of A2 to A10 for the partial string 'excel'. In this case, the formula will count any cells that contain the word Excel, regardless of where the word happens.

Excel gives you the option of two wildcard operators when working with the COUNTIF() function:

1. ? represents a single-character wildcard. This can be helpful when you know you only want one character to be different.
2. * represents multiple-character wildcards. This means that it doesn’t matter how many characters are different from the substring.

Let’s take a look at how this works in Excel:

In the example above, we used two wildcard operators to search for a specific substring. Keep in mind that Excel offers two different types of wildcard operators that allow you to narrow down how to search for partial strings.

Count Cells That Start With Specific Text in Excel

In order to count cells that start with a specific string of text, you can use the COUNTIF() function in combination with a wildcard operator. For example, if you’re searching for a string of text that starts with “North”, you can use the following formula:

=COUNTIF(A2:A10, "North*")

The example function above lets you search for any cell that simply starts with the string North. For example, this could include cells that contain Northwest or Northeast.

Let’s take a look at an example of how to count cells that start with a specific substring:

In the example above, we counted cells that started with the string “that”. By using a wildcard operator at the end of the criteria, we force Excel to search for the substring at the beginning of a string of text. Let’s now take a look at how to count cells that end with specific text.

Count Cells That End With Specific Text in Excel

Similar to the example above, we can use Excel to count cells that end with a specific string of text. This works in a very similar way – instead of placing the wildcard at the end of the substring, we’ll place the wildcard at the beginning of the substring.

For example, if you’re counting cells that end in the substring ‘site’, you could use the following formula:

=COUNTIF(A2:A10, "*site")

Let’s see what this formula looks like in Excel by using an example:

In the example above, we used the wildcard operator to count cells that end with a particular text. By placing the wildcard in front of the text we want our string to end with, we instruct Excel to search for cells where the last four characters are ‘site’.

In the final section below, you’ll learn how to use Excel to count cells that contain any text at all.

Count Cells That Contain Any Text in Excel

If you’re hoping to count cells that only contain text (meaning they only contain alphabetical characters), the Excel COUNTIF() function won’t work. However, we can use easily use two other functions to accomplish this: SUM() and ISTEXT(). The ISTEXT() function checks whether or not a value is only text and returns a True or False value.

In order to convert a True or False value into a number, you can prefix the value with --. This forces the value to be converted to a number and then turns any negative values positive. Because True is equal to 1, the first - converts it to -1, then to positive 1. From there, you can pass the values into the SUM() function.

Let’s take a look at an example of how this works in Excel:

In the example above, we used the SUM() and ISTEXT() functions to count how many cells are only alphabetic.

Conclusion

In this tutorial, you learned how to count cells that contain text. This has many use cases, including being able to count how often a user logged in or how many times a salesperson made a sale. You first learned how to use the COUNTIF() function to count cells that contain a specific or partial string of text. From there, you learned how to count cells that start or end with a particular string of text. Finally, you learned how to count cells that contain only text, rather than also numbers.

Additional Resources

To learn more about related topics, check out the tutorials below:

• Excel COUNTIF(): How to Use the Excel COUNTIF() Function
• How to Count Not Blank Cells in Excel with COUNTIF()
• Excel COUNTIF() Function: Official Documentation