Count Characters in a Cell
Counting what is inside one cell and counting how many cells hold something are separate jobs with separate functions. Choosing the wrong side is why an answer comes back baffling rather than merely wrong. LEN and its relatives measure the text within a cell; COUNTA, COUNTIF and the rest tally cells and never look inside one. Stay on the character side and every variation turns out to be the same manoeuvre performed twice — measure the text, delete part of it, measure again, and the shortfall is your answer — which is how you tally a single letter, a repeated word, or the gaps that stand in for word breaks. The asymmetry to watch is that SUBSTITUTE, which does the deleting, matches capitals exactly, while the COUNTIF family on the other side of the fork disregards them entirely; the same search therefore answers differently depending on which question you asked, and neither side raises an error to say so.
The formula
=LEN(A2) every character, spaces included
=LEN(SUBSTITUTE(A2," ","")) characters with the spaces taken out
=LEN(A2)-LEN(SUBSTITUTE(A2,"s","")) how many times "s" appears — case-sensitive
=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"S","")) the same tally, ignoring case
=(LEN(A2)-LEN(SUBSTITUTE(A2,"ss","")))/LEN("ss") occurrences of a multi-character string
=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1) words, with an empty cell scoring 0 rather than 1
=SUMPRODUCT(LEN(A2:A100)) the combined character count of a whole columnA worked example
A2 holds the string "Mississippi River" — one space, four lower-case s characters, and no capital S anywhere in it.
=LEN(A2), =LEN(SUBSTITUTE(A2," ","")), =LEN(A2)-LEN(SUBSTITUTE(A2,"s","")) and =(LEN(A2)-LEN(SUBSTITUTE(A2,"ss","")))/LEN("ss")
17, 16, 4 and 2. Change the third formula's "s" to "S" and it returns 0, because SUBSTITUTE compares case exactly — =LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"S","")) is the version that answers 4 whichever case you type. The fourth divides by the length of the string it searched for, without which every two-character match would be counted as two. And the word count, =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1, gives 2: one gap, so two words.
Which one do I need?
| If you want to… | Use |
|---|---|
| Characters in one cell, spaces and punctuation included | =LEN(A2). It measures the stored value, so a cell displaying 3.14 while holding 3.14159265 counts as 10 |
| Characters with the spaces excluded | =LEN(SUBSTITUTE(A2," ","")) — SUBSTITUTE strips them out, LEN measures whatever survives |
| How many times one particular character appears | =LEN(A2)-LEN(SUBSTITUTE(A2,"x","")) — the length that went missing is the count |
| That tally should ignore capitals | =LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"X","")) — on its own SUBSTITUTE is case-sensitive |
| A whole word or a two-character code rather than a single character | Divide by its length: =(LEN(A2)-LEN(SUBSTITUTE(A2,"ss","")))/LEN("ss") |
| Words instead of characters | =IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1) — TRIM stops double spaces inflating it, the IF stops an empty cell claiming one word |
| A single total across a column rather than a figure per row | =SUMPRODUCT(LEN(A2:A100)), which measures every cell and adds the lengths in one go |
| How many cells hold something, not how many characters sit inside one | A different question entirely — COUNTA, COUNT and COUNTBLANK all count cells |
| How many cells contain a given word | =COUNTIF(A2:A100,"*invoice*") — cells rather than occurrences, and case-insensitive, which is the opposite of SUBSTITUTE |
| LEN reports more characters than you can see | Trailing spaces, a non-breaking CHAR(160) pasted from a web page, or a CHAR(10) line break all count. Compare against =LEN(TRIM(A2)) and =LEN(SUBSTITUTE(A2,CHAR(160),"")) to find which |
| You want to extract the characters, not count them | LEFT, RIGHT and MID take a count and hand back the text itself |
Frequently asked questions
Why does my count come back as 0 when I can see the character?
Almost always capitals. SUBSTITUTE matches them exactly, so hunting for a lower-case letter passes straight over every upper-case one and reports nothing, with no error to hint at the cause. Fold the case before measuring — =LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"S","")) — and the count is right whichever way the text was typed. The habit trips people because the COUNTIF family, which most people meet first, behaves the opposite way and never distinguishes case at all.
How do I count the cells containing a word rather than how often it appears?
That is the other side of the fork, and it needs a different function entirely. =COUNTIF(A2:A100,"*invoice*") reports how many cells mention the word, counting a cell once however many times the word occurs inside it; the LEN-and-SUBTRACT construction reports occurrences within a single cell and knows nothing about the range around it. Deciding which number the question actually asks for is most of the work.
Why does LEN return more characters than the cell displays?
Because it measures what is stored, not what is shown. Trailing spaces, a non-breaking space imported as CHAR(160), and a CHAR(10) line break from Alt+Enter are all real characters that LEN counts and your eye does not. Numbers and dates make it worse: a cell formatted to two decimals still holds its full precision, and a date cell holds a serial number, so LEN measures that instead of the date you see.
Is there a limit to how many characters a cell can hold?
Yes — 32,767 characters in a single cell, which is also the ceiling LEN can ever report. The formula bar shows all of them, but the grid will not render that much inside a cell however wide you make the column. A text argument being silently cut short is usually a different limit: worksheet names cap at 31 characters and headers and footers at 255.
New guides and tools, once a month
DE + EN · double opt-in · no spam