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.
In cell E3, enter the following formula:
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):
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:
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:
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:
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!
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?
Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.