Count Text in Excel – Characters, Words, & Cells With Text

Child sitting in front of a computer screen learning how to count text in Excel


Need to know the word count of a spreadsheet you’re working on, or maybe the number of cells that contain text? Even though there’s no built-in counting tool, you can still count text in Excel using a few simple formulas!

Count the Number of Characters in a Cell or Range

Using the LEN function is one of the easiest ways to count text in Excel, including all characters – spaces, letters, and numbers – within a single cell or a range. To see how it works, let’s start with a dataset like the one in the example below.

CountTextInExcel.xlsx

Excel table that shows various fruits, product numbers, and location Zip codes

In cell E3, enter the following formula:

=LEN(B3:D3)

The values are automatically split between three cells that show the number of characters in the corresponding range. 

Excel table that shows various fruits, product numbers, and location Zip codes next to a formula containing the LEN function

Another neat thing you can do with the LEN function is count the number of instances of a specific character. For example, if you want to see how many times the letter A is in cell B3, you could use this formula (just note that it’s case-sensitive):

=LEN(B3)-LEN(SUBSTITUTE(B3,"A",""))

Count the Number of Words in Excel

The formula for finding the word count in Excel depends on the specific data you’re trying to count. If you want to count the number of words in a single cell, like cell B3 in our example, you can use the following formula:

=LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1

This formula works by counting the number of spaces in the cell, and then adding one to get the total number of words.

Since there’s only one word, “Apple,” currently, in cell B3, the result will be one. But, you can add a few words like “Granny Smith Apple” to see the count update to three words.

Excel table that shows various fruits, product numbers, and location Zip codes that shows how to count text in Excel

Count The Number of Cells With Text in Excel

The COUNTIF and SUMPRODUCT functions in Excel can both be used to count the number of cells that meet particular criteria. In this case, we’ll use them to count the number of cells that contain text only. 

Count Text in Excel with COUNTIF

To count the number of cells that contain text, first select the range of cells that you want to count. In this case, that range is B3:D15.

In an empty cell below the table, enter this formula:

 =COUNTIF(B3:D15,"*")
Excel table that shows various fruits, product numbers, and location Zip codes that shows how to count text in Excel

You’ll see that the COUNTIF function counted the number of cells in the range that contain any text, regardless of the length or content.

Since the asterisk (*) is a wildcard character that represents any sequence of characters, the formula tallies all cells that have any type of text. You can learn more about wildcard characters in our post How to Count Cells That Contain Specific Text.

If you want to count the numbers, you can use the formula =COUNT(B3:B15) since the COUNT function only counts numeric values.

Count Text in Excel with SUMPRODUCT

You can also count the number of cells that contain text with the SUMPRODUCT and ISTEXT functions. Here’s what that formula looks like used in our example:

=SUMPRODUCT(--ISTEXT(B3:D15))

The SUMPRODUCT formula can be used to multiply two arrays of data: one that contains 1 for cells with text and 0 for cells without text, and another that contains the total number of cells in a given range.

The ISTEXT function returns an array of TRUE or FALSE values for each cell in the range. The array contains TRUE for cells with text and FALSE for cells without text. The double negative operator (–) then converts the TRUE and FALSE values to 1s and 0s, respectively.

Finally, the two arrays are multiplied together using the SUMPRODUCT formula, which adds up the resulting array of products. The result is the total number of cells in the range that contain any type of text.


Do you have any other tips for counting text in Excel? Let us know in the comments!

Posted in
Avatar photo

Excel University

We love sharing the things we've learned about Excel, and we built Excel University to help us do that. Our motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

1 Comment

  1. David N on September 26, 2023 at 3:10 pm

    Just be careful. The technique for substituting/counting spaces to get a word count can easily be fooled by the spaces before and after any hyphens or dashes — such as the ones I’m using right now — that are used to offset a parenthetical clause. It can also be fooled by those who use a double space to separate sentences as I did to separate these two sentences. And there may even be other valid uses of additional spaces that I’m not thinking of right now.

Leave a Comment