LEN function
Quick answer
LEN counts the characters in a text string, including spaces and punctuation.
- Syntax
=LEN(text)- Most common use
=LEN(A2)returns 10 (The trailing space counts: 10 characters, not 9)
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
Answers that use LEN
In other languages
LEN is LARGO in Spanish Excel and LÄNGE in German Excel. Each page has the syntax with semicolons, the examples and the exercise in that language. For other functions and languages, use the Function Translator.
Updated Checked by running: every formula on this page was run by our formula engine on .