How to count characters in Excel and Google Sheets
Use =LEN(A2) to count every character in a cell, including spaces and punctuation. To count characters across a whole range use =SUMPRODUCT(LEN(A2:A100)). To count how many times one specific character appears, subtract the length with that character removed: =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")). All three work identically in Excel and Google Sheets.
Count every character in a cell
=LEN(A2)Counts spaces, punctuation and trailing blanks — LEN does not trim anything first.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Count characters in one cell
LEN takes a single value and returns the number of characters in it. It counts everything: letters, digits, spaces, punctuation and any invisible characters that came along with a paste.
That last point is what usually surprises people. If LEN returns a bigger number than you expect, the cell almost certainly has leading, trailing or double spaces in it. Wrap the reference in TRIM to measure the visible text instead.
Straight count, spaces included
=LEN(A2)Ignore leading, trailing and repeated spaces
=LEN(TRIM(A2))TRIM removes leading and trailing spaces and collapses runs of spaces inside the text down to one.
Count letters only, ignoring spaces entirely
=LEN(SUBSTITUTE(A2," ",""))Removes every space before measuring, so "New York" returns 7 rather than 8.
Count characters across a whole range
LEN is built for one value at a time. Pointing it at a range and wrapping it in SUM does not work in older Excel without array entry, which is where most people get stuck.
SUMPRODUCT is the reliable answer because it evaluates the array without needing Ctrl+Shift+Enter, and it behaves the same in every Excel version and in Google Sheets.
Total characters in a range
=SUMPRODUCT(LEN(A2:A100))Works in every version of Excel and in Sheets, with no array entry.
Correct in Excel and Sheets. Our verifier has no implementation for this form, so it was checked by hand.
Total characters, modern Excel and Sheets
=SUM(LEN(A2:A100))Only safe on Excel 365 / Excel 2021 or Google Sheets, where formulas spill natively. In Excel 2019 and earlier this needs Ctrl+Shift+Enter.
Correct in Excel and Sheets. Our verifier has no implementation for this form, so it was checked by hand.
Average characters per cell, ignoring blanks
=SUMPRODUCT(LEN(A2:A100))/COUNTA(A2:A100)Correct in Excel and Sheets. Our verifier has no implementation for this form, so it was checked by hand.
Count how many times one character appears
Counting how many times one character appears in a cell has no dedicated COUNTCHAR function in Excel or Google Sheets. Measure the text with LEN, remove every instance of the character with SUBSTITUTE, measure the result again, and take the difference between the two lengths.
For a multi-character string, divide by the length of that string — otherwise removing a three-character word would count as three hits rather than one.
Count one character (case-insensitive)
=LEN(A2)-LEN(SUBSTITUTE(UPPER(A2),"A",""))UPPER normalises the case first, so "a" and "A" both count.
Count one character (case-sensitive)
=LEN(A2)-LEN(SUBSTITUTE(A2,"a",""))SUBSTITUTE is case-sensitive by default, so this counts lowercase "a" only.
Count a whole word or multi-character string
=(LEN(A2)-LEN(SUBSTITUTE(A2,"the","")))/LEN("the")Dividing by the length of the search string turns 3 removed characters back into 1 occurrence.
Count commas, to check a delimited list
=LEN(A2)-LEN(SUBSTITUTE(A2,",",""))A list of n items separated by commas has n-1 commas, so add 1 for the item count.
Count words instead of characters
Count space-separated words with =IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1). TRIM removes leading and trailing ordinary spaces and collapses repeated ones. LEN measures the trimmed text before and after SUBSTITUTE removes the remaining spaces; the difference is the number of gaps between words. Adding one gives the word count.
The IF guard makes an empty or ordinary-space-only cell return 0 instead of 1. A single word returns 1, and "North America" returns 2 even with doubled ordinary spaces. Hyphenated terms such as "well-known" count as one token. Tabs, line breaks, and non-breaking spaces are not handled by this space-only formula; normalize those separators first if they occur in your data.
Words in a cell
=IF(TRIM(A2)="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)The IF guard matters: without it, a blank cell reports one word.
Why your count looks wrong
Three causes account for nearly every mismatch between what LEN reports and what you can see on screen.
Non-breaking spaces from a web paste are character 160, not 32, so TRIM leaves them in place. Strip them with SUBSTITUTE(A2,CHAR(160),"") first — in Google Sheets use CHAR(160) the same way.
A number formatted as text still counts its formatting characters if they are literally in the cell, but not if they come from a number format. LEN sees the underlying value, so a cell displaying "$1,200.00" from a currency format returns 4, not 9.
Line breaks inside a cell are a character too — CHAR(10) on both platforms. They count toward LEN even though they render as height rather than width.
Strip non-breaking spaces before counting
=LEN(TRIM(SUBSTITUTE(A2,CHAR(160)," ")))Count line breaks in a cell
=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))Which formula for which question
| What you want | Formula | Same in Sheets? |
|---|---|---|
| Characters in one cell | =LEN(A2) | Yes |
| Characters, ignoring stray spaces | =LEN(TRIM(A2)) | Yes |
| Characters, ignoring all spaces | =LEN(SUBSTITUTE(A2," ","")) | Yes |
| Characters across a range | =SUMPRODUCT(LEN(A2:A100)) | Yes |
| Times one character appears | =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")) | Yes |
| Times a word appears | =(LEN(A2)-LEN(SUBSTITUTE(A2,"the","")))/LEN("the") | Yes |
| Words in a cell | =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 | Yes |
Frequently asked
Is there a COUNTCHAR function in Excel?
No. Neither Excel nor Google Sheets has a dedicated character-counting function beyond LEN. Counting a specific character is done with the LEN/SUBSTITUTE difference technique shown above.
Does LEN count spaces?
Yes. LEN counts every character including spaces, punctuation, line breaks and any invisible characters. Wrap the reference in TRIM to exclude leading, trailing and duplicated spaces.
Why does LEN return more characters than I can see?
Almost always trailing spaces or non-breaking spaces pasted in from a web page. TRIM removes ordinary spaces; non-breaking spaces are CHAR(160) and need SUBSTITUTE(A2,CHAR(160),"") to clear.
How do I count characters in a whole column?
Use =SUMPRODUCT(LEN(A2:A100)). SUMPRODUCT evaluates the array without needing Ctrl+Shift+Enter, so it works in every Excel version and in Google Sheets.
Do these formulas work the same in Google Sheets?
Yes. LEN, TRIM, SUBSTITUTE, SUMPRODUCT, UPPER and CHAR all exist in Google Sheets with identical syntax, so every formula on this page transfers unchanged.
Stop writing these by hand
Describe what you need in plain English and FormulaCraft returns the formula, already run through a real spreadsheet engine. Free, no account.
Related
Last reviewed 2026-09-15. Every formula here is executed against sample data by our verification engine as part of the build. The few that cannot be — because they need values from your own sheet, or because the engine has no implementation for that form — say so directly underneath, rather than being counted as verified.