Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to count characters in a cell in Excel and Google Sheets
Excel & Google Sheets
=LEN(A2)✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Text |
| Hello World |
| FormulaCraft |
| Excel 365 |
| Go |
| Spreadsheet tips |
=LEN(A2)→11
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
Runs server-side · free · no signup
Step by step
- 1Click the cell where you want the character count.
- 2Enter =LEN(A2), pointing at the cell with the text.
- 3Press Enter. The result includes spaces and punctuation.
- 4To exclude spaces: =LEN(SUBSTITUTE(A2," ","")) removes spaces first, then counts.
- 5To total characters across a range: =SUMPRODUCT(LEN(A2:A6)).
Tips
- LEN counts spaces as characters. Wrap in TRIM to ignore leading/trailing whitespace: =LEN(TRIM(A2)).
- LEN counts every Unicode character; some emoji count as 2 because they're stored as surrogate pairs.
- Count how many times a specific character appears: =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")). Use UPPER on both sides if you don't care about case.
- Count words instead of characters: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1.
- LEN returns 0 for empty cells and works on numbers too (it converts them to text first).
- To count characters across an entire column ignoring blanks: =SUMPRODUCT(LEN(A2:A1000)).
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
Frequently asked
Does LEN count spaces?
Yes — every space is one character. To count without spaces, use =LEN(SUBSTITUTE(A2," ","")). To ignore only leading and trailing spaces, use =LEN(TRIM(A2)).
How do I count characters in a range of cells?
Use =SUMPRODUCT(LEN(A2:A6)) — it sums the character length of every cell in the range and returns one number.
How do I count a specific character?
Use =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")). The trick: SUBSTITUTE removes every "a"; the difference in length is how many were there. For case-insensitive, wrap both LEN arguments in UPPER.
How do I count words in a cell, not characters?
Use =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1. It counts the spaces between words and adds one. TRIM handles double-spaces or stray padding.
Why does my emoji count as 2 characters?
Most emoji are stored as surrogate pairs in UTF-16, so LEN sees two code units. There's no built-in fix in Excel; in Google Sheets, REGEXEXTRACTALL with a Unicode-aware pattern is the workaround.
What's the difference between LEN and LENB?
LEN counts characters; LENB counts bytes. For ASCII text they match. For double-byte characters (Japanese, Chinese, Korean) LENB returns twice the character count. Use LEN unless you're dealing with multi-byte text on a system with DBCS support.