Count Text in Excel: Best Formulas for Cells, Characters, and Words

If you are trying to count text in Excel, the hard part is not the formula. It is figuring out what you actually want to count. Sometimes you need the number of cells that contain text. Sometimes you need character count, word count, or the number of times a specific word appears. Once that part is clear, Excel gets much easier.

Disclosure: This page contains affiliate links. If you buy through them, we may earn a commission at no extra cost to you.

Quick answer

Use =COUNTIF(A2:A100,"*") to count cells that contain text, =LEN(A2) to count characters in one cell, =COUNTA(TEXTSPLIT(TRIM(A2)," ")) to count words in newer Excel, and =COUNTIF(A2:A100,"*apple*") to count cells containing specific text. If you only need non-empty cells, use COUNTA, but remember it also counts numbers, errors, and formulas that return an empty text value.

Function availability can vary by Excel version, so check Microsoft Support for the latest details.

Many articles ranking for this topic answer only one version of the question. In real spreadsheets, people usually mean one of four things when they search 'count text in Excel': count text cells, count characters, count words, or count a specific word or phrase. This cheat sheet covers all four.

Best formulas to count text in Excel

Goal	Formula	What it counts	Best when
Count cells with any text	=COUNTIF(A2:A100,"*")	Cells containing text	You want to ignore numbers and blanks
Count nonblank cells	=COUNTA(A2:A100)	Anything not empty	You want filled cells, not text only
Count characters in one cell	=LEN(A2)	Letters, numbers, punctuation, and spaces	You need title or message length
Count words in one cell	=COUNTA(TEXTSPLIT(TRIM(A2)," "))	Words after trimming extra spaces	You use newer Excel
Count cells containing a term	=COUNTIF(A2:A100,"apple")	Cells where the term appears anywhere	You need contains logic

The main takeaway is simple: there is no single universal count-text formula because 'text' can mean different things. Pick the right target first, then apply the matching formula.

Track text limits in one shared workspace

Use docs, tables, and automations to manage copy length without rebuilding spreadsheet formulas every time.

Try Coda

How to count cells with text in Excel

The fastest formula for text-only cells is =COUNTIF(A2:A100,"*"). The asterisk is a wildcard, so Excel counts any cell that contains text. This is the best starting point when your column includes names, comments, statuses, or notes.

Use =COUNTIF(A2:A100,"apple") when you need an exact text match, and use =COUNTIF(A2:A100,"*apple*") when you want to count cells that contain that word or phrase anywhere inside the cell. That second version is useful for tags, imported notes, search terms, and messy exports.

If your range mixes text and numbers and you want a stricter text-only check, use =SUMPRODUCT(--ISTEXT(A2:A100)). This works well when the wildcard route is not precise enough for your worksheet.

Be careful with =COUNTA(A2:A100). COUNTA counts anything that is not empty, including numbers, errors, and formulas that return an empty text value. It is perfect for nonblank counts, but it is not the same as counting text cells.

How to count characters in Excel

To count characters in one cell, use =LEN(A2). LEN counts letters, numbers, punctuation, and spaces, so it is the right option when you need the full length of a title, caption, description, or message.

To count characters across a range, use =SUMPRODUCT(LEN(A2:A10)). That adds the character count from each cell and returns one total. If you need a character count without spaces, use =LEN(A2)-LEN(SUBSTITUTE(A2,"

Turn one-off formulas into a repeatable workflow

Start with Coda

Step-by-step: choose the right formula fast

Define what 'text count' means in your sheet. Do you need text cells, total characters, total words, or occurrences of one term?
Test the formula on a small sample before applying it to the full range.
Clean spacing first with TRIM if your data came from forms, exports, or copied web text.
Decide whether numbers should count. If yes, COUNTA may be correct. If no, use COUNTIF with a wildcard or ISTEXT.
Check one edge case before you finish: blank cells, formulas returning empty text, and punctuation around words can all change the result.

Mistakes to avoid

Using COUNTA when you really need text only. COUNTA counts nonblank cells, not just text.
Forgetting that LEN counts spaces. If you need visible characters only, subtract spaces with SUBSTITUTE.
Ignoring messy spacing. Extra spaces can break word counts, so wrap the cell in TRIM first.
Assuming partial matches are whole-word matches. COUNTIF with wildcards will count 'apple' inside 'pineapple'.
Using the wrong formula separator. In some regions Excel uses semicolons instead of commas.
Trusting formulas that return empty text. A formula that looks blank may still be counted by COUNTA.

FAQ

How do I count cells with text but ignore numbers?

Use =COUNTIF(A2:A100,"*") for most cases. If your worksheet is more complex, use =SUMPRODUCT(--ISTEXT(A2:A100)) for a stricter text-only count.

Does LEN count spaces in Excel?

Yes. LEN counts letters, numbers, punctuation, and spaces. If you want a count without spaces, subtract them with SUBSTITUTE.

What is the easiest way to count words in one cell?

In newer Excel versions, use =COUNTA(TEXTSPLIT(TRIM(A2)," ")). In older versions, use the LEN and SUBSTITUTE method with a blank-cell check.

Why is my COUNTA result higher than my COUNTIF text count?

Because COUNTA counts any nonblank value, including numbers, errors, spaces, and formulas that return empty text. COUNTIF with "*" is narrower and focuses on text cells.

Can I count a specific word across a range?

Yes. If you want the number of cells containing that word, use =COUNTIF(A2:A100,"*apple*"). If you need exact occurrence counts inside each cell, use the LEN and SUBSTITUTE approach.

Conclusion

There is no single 'count text' formula in Excel because the job can mean several different things. Once you separate the problem into text cells, characters, words, or specific terms, the right formula becomes simple. Start with the cheat sheet, test one small range, and then apply the formula across your sheet. If you do this repeatedly for content or operations work, documenting the logic in a shared system will save time and reduce errors.