LEN - Measure text length, including spaces
Check imported reference lengths and distinguish a character count from a numeric value or a count of populated cells.
By the end, you can
- Count characters in an identifier.
- Include spaces and punctuation in the count.
- Use length checks without claiming complete validation.
Before you start: Read text values, separators, and cell references.
In this lesson
How long is this reference?
LEN returns the number of characters in a text value. A CRM import may require an eight-character employee reference, or an accounts sheet may need the length of each customer key. The result is a number describing length, not the original code or the number of records in a column.
Syntax and argument
=LEN(text)Supply one text value or cell. Spaces and punctuation are part of its length.
| Argument | Purpose | Example |
|---|---|---|
textRequired | The value whose character length you want. | A2 |
An imported key-length check
8| A | B | |
|---|---|---|
| 1 | Reference | Characters |
| 2 | EMP-1042 | 8 |
| 3 | DL-29 | 5 |
| 4 | CLIENT-301 | 10 |
=LEN(A2)EMP contributes three characters, the hyphen one, and 1042 four: 3+1+4=8.
- Select B2 alongside the reference.
- Enter =LEN(A2), then fill down.
- Manually check the shortest code: DL-29 has 2+1+2=5 characters.
- Investigate unexpected lengths against the import specification, not an assumption that all keys share one format.
Spaces, empty text, and a check label
=LEN("Anika Rao")Five name letters, one space, and three surname letters give nine characters.
=LEN("")A zero-length text value has length zero, even though COUNTA can count it as populated.
=IF(LEN(A2)=8,"Length OK","Review")EMP-1042 passes the length rule. That does not independently validate its prefix or uniqueness.
Characters are not bytes or formatted values
- Leading, internal, and trailing spaces all count. Tabs and line breaks can also contribute even when hard to see.
- A genuinely blank cell has length zero. Error inputs propagate instead of becoming a useful length.
- Numbers can be converted to text for LEN, but numeric display formats such as currency symbols or added leading zeroes are not reliable source characters. Keep IDs as text.
- LEN is available in older/current Excel. In workbook Compatibility Version 2, surrogate pairs count as one; variation selectors remain separate. A visible symbol may still comprise several Unicode characters.
Common mistakes and Practice scope
The linked task uses ASCII customer references. Its results do not establish Microsoft Excel's workbook-version Unicode counting behavior; the custom engine counts JavaScript string units.
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.