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

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

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. 

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.

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,"*")

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!