LEN function
LEN counts the characters in a text string, including spaces and punctuation. It's the measuring tape behind most text formulas: checking that IDs are the right length, finding stray spaces, and working out how many characters to keep with LEFT, RIGHT or MID.
- Available in
- Every current version
- Category
- Text functions
- Returns
- A number: the count of characters. An empty cell returns 0.
- Google Sheets
- Works the same in Google Sheets.
Syntax
=LEN(text)
| Argument | Required? | What it is |
|---|---|---|
text | Required | The text or cell to measure. Numbers are counted as stored, without formatting. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| D2 | =LEN(A2) | 10 | The trailing space counts: 10 characters, not 9 |
| D3 | =LEN(B3)=5 | FALSE | TRUE only for a five-digit ZIP; this one is a digit short |
| D4 | =280-LEN(C4) | 252 | Characters left under a 280-character limit |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Name | ZIP | Post | Result |
| Maya Chen | 55409 | Launching our spring line today! | 10 |
| Raj Patel | 5541 | Sale ends Friday | FALSE |
| Lee Wong | 55416-2207 | New store hours start Monday | 252 |
Practice LEN
Graded instantly, then re-checked on changed data.
Check ID lengths
B2, return TRUE if the ID in A2 has 8 characters and FALSE if not. Fill down to B6.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| The count is one or two too high | Hidden leading or trailing spaces count. Compare LEN(A2) with LEN(TRIM(A2)); if they differ, the cell has extra spaces. |
| Formatted numbers count short | LEN counts the stored value, so $1,250.00 stored as 1250 returns 4. Use LEN(TEXT(A2,"$#,##0.00")) to count what you see. |
| A range gives several answers | =LEN(A2:A10) returns one length per cell, which spills in Microsoft 365. To total the characters in a range, use =SUMPRODUCT(LEN(A2:A10)). |
Tips
- Count how many times a character appears: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")) counts the commas.
- Cap a field's length with data validation using a custom rule such as =LEN(A2)<=50.
Questions
Does LEN count spaces?
Yes. Every space counts, including leading and trailing ones you can't see, which makes LEN a quick way to spot messy imports.
How do I count words in a cell?
Count the spaces in the trimmed text and add one: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1. An empty cell returns 1 with this formula, so wrap it in IF if blanks are possible.
Lessons that use LEN
Last reviewed October 2026. Every example result on this page is calculated live by the Spreadsheet Practice formula engine.