How To Count Text In Excel

Excel is a powerful tool not only for numbers but also for working with text. Sometimes, you may want to know how many cells in a range contain text — for example, if you are tracking responses, names, or categories. Fortunately, Excel has simple ways to count text without needing advanced formulas. Here’s a clear guide how to count text in Excel.

1. Use the COUNTA Function

The most basic way to count non-empty cells (including those with text) is by using the COUNTA function.

• Type =COUNTA(range) where range is the group of cells you want to check, such as A1:A10.
• This will count all non-empty cells, including text, numbers, and errors.
• Keep in mind, it does not distinguish between text and numbers.

2. Use the COUNTIF Function for Text Only

If you specifically want to count cells that only contain text (ignoring numbers), the COUNTIF function works well.

• Type =COUNTIF(range, “*”).
• The asterisk (*) is a wildcard that represents any sequence of characters, so this formula counts all cells with text.
• For example, =COUNTIF(A1:A10, “*”) counts how many cells from A1 to A10 contain text.

3. Exclude Blanks or Numbers

Sometimes, you want to exclude empty cells or cells with numbers.

• To count only text (no numbers or blanks), you can combine COUNTIF with ISNUMBER or use an array formula, but for most people, sticking with =COUNTIF(A1:A10, “*”) is enough.
• If you are using Excel 365 or Excel 2021, you can also use =COUNTIFS(A1:A10, “*”, A1:A10, “<>0”) to exclude zeros.

4. Check for Specific Text

If you want to count how many times a specific word or text appears, COUNTIF helps again.

• Use =COUNTIF(range, “text”) where text is the exact word or phrase you’re looking for.
• For example, =COUNTIF(A1:A10, “Completed”) counts how many cells exactly say “Completed.”

5. Count Words Inside a Cell

If you want to count how many words are inside a single cell, you can use a combination of formulas.

• Use =LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,” “,””))+1.
• This counts the number of spaces and adds one, giving you the number of words.
• Remember, this works best if the cell has clean, trimmed text.

Also Read: How To Connect A Washing Machine