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.
- 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.
- 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.
- 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.
- 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.”
- 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