=LEN(A2) returns the number of characters in A2. Every character counts: letters, digits, spaces, punctuation and line breaks. Hello world has 11.
| A | B | |
|---|---|---|
| 1 | Text | Characters |
| 2 | Hello world | 11 |
| 3 | Hello world | 13 |
| 4 | Excel | 5 |
| 5 | 0 |
A3 has a space at each end, so it counts 13. A5 is empty, and LEN of an empty cell is 0. The syntax has one argument: =LEN(text), where text is a cell, a formula, or text in quotes.
LEN counts the stored value, not the display
For numbers and dates, LEN counts the value Excel stores, which is not always what you see:
| A | B | C | |
|---|---|---|---|
| 1 | Value | LEN | LEN of the display |
| 2 | $1,050.00 | 4 | 9 |
| 3 | 2026-03-15 | 5 | 10 |
A2 shows $1,050.00 but is stored as 1050, so LEN returns 4. The date is stored as the serial number 46096, so LEN returns 5. To count the characters as displayed, convert with TEXT first, as column C does.
Count words in a cell
Excel has no word count function. Words are separated by spaces, so count the spaces and add one. TRIM first, so that double spaces and spaces at the ends are not counted as words:
| A | B | |
|---|---|---|
| 1 | Text | Words |
| 2 | The quick brown fox | 4 |
| 3 | two spaces | 2 |
| 4 | one | 1 |
| 5 | 0 |
How it works, for The quick brown fox: the trimmed text has 19 characters; without spaces, 16. Three spaces mean four words. The IF in front makes an empty cell count 0 words instead of 1.
| A | B | |
|---|---|---|
| 1 | Sentence | Words |
| 2 | Payment received with thanks |
Your turn: In B2, count the words in A2. The words are separated by single spaces.
Check a character limit
LEN is how you check text against a limit: a page title of 60 characters, an SMS of 160, a product name of 30. Characters left is the limit minus LEN, and conditional formatting can flag the rows over the limit:
| A | B | C | |
|---|---|---|---|
| 1 | Title | Characters | Left |
| 2 | Desk lamp, black | 16 | 14 |
| 3 | Ergonomic office chair with headrest | 36 | -6 |
| 4 | Monitor stand | 13 | 17 |
The rule =LEN($A2)>30 colours the whole row of any title over the limit. The $ before A keeps every column of the row looking at the title. Edit A3 down to 30 characters and the colour goes away. To refuse longer text at entry, use Data > Data Validation and set Allow to Text length.
| A | B | |
|---|---|---|
| 1 | Name | Left |
| 2 | Wireless mouse |
Your turn: Product names may have at most 40 characters. In B2, return how many characters are left for the name in A2.
Count characters in a range
LEN works on one cell. Give it a range inside SUM and it counts each cell, then SUM adds them:
| A | B | C | |
|---|---|---|---|
| 1 | Text | Method | Total |
| 2 | Apple | SUM | 15 |
| 3 | Pear | SUMPRODUCT | 15 |
| 4 | Banana |
=SUM(LEN(A2:A4)) works as it is in Excel 2021 and Microsoft 365. In Excel 2019 and older it needs Ctrl+Shift+Enter (Cmd+Shift+Enter on a Mac), or use SUMPRODUCT, which handles arrays on its own.
To count one character inside a cell, such as the commas in a list, compare the length before and after removing it: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")). The SUBSTITUTE page has a live example, including how to count a whole word.
Frequently Asked Questions
How do I count characters in an Excel cell?
=LEN(A2) returns the number of characters in A2. Spaces, punctuation and line breaks all count. To ignore extra spaces, count the trimmed text: =LEN(TRIM(A2)).
How do I count words in a cell in Excel?
Count the spaces and add one: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1. Wrap it as =IF(TRIM(A2)="",0,...) so an empty cell counts 0 words, not 1.
Why does LEN return the wrong count for a number or a date?
LEN counts the stored value, not what the cell shows. #,##0.00"))` to count the displayed form.
How do I count all characters in a range?
=SUM(LEN(A2:A10)) in Excel 2021 and Microsoft 365. In older versions use =SUMPRODUCT(LEN(A2:A10)), which works without Ctrl+Shift+Enter.
