The LEN function returns the number of characters in a text string. This guide explains how LEN works and includes practical examples you can use immediately.
WHAT THE LEN FUNCTION DOES
LEN counts every character in a text string, including letters, numbers, spaces, and special characters. It's useful for validating input length, building dynamic text extraction formulas with LEFT/RIGHT/MID, or checking data quality in spreadsheets.
WHEN TO USE LEN
- You want to validate that text meets length requirements (passwords, codes)
- You need to count characters for text analysis or reporting
- You're building dynamic formulas that depend on text length
- You want to identify unusually long or short entries
FORMULA SYNTAX
LEN(text)| Argument | Description |
|---|---|
text | The text string whose length you want to find |
EXAMPLE 1: VALIDATE PASSWORD LENGTH
We want to check if passwords meet the minimum 8-character requirement.
| A | B | C | |
|---|---|---|---|
| 1 | Password | Length | Valid? |
| 2 | abc123 | =LEN(A2) | =IF(B2>=8,"Yes","No") |
| 3 | password123 | =LEN(A3) | =IF(B3>=8,"Yes","No") |
| 4 | 12345 | =LEN(A4) | =IF(B4>=8,"Yes","No") |
=LEN(A2)Result: 6, 11, 5 characters respectively ("No", "Yes", "No" for validity)
Need help applying this? Try this in Numstro Builder
EXAMPLE 2: DYNAMIC TEXT EXTRACTION USING LEN
We want to extract everything except the last 4 characters (like hiding account numbers).
| A | B | |
|---|---|---|
| 1 | Account Number | Masked |
| 2 | 1234567890 | =LEFT(A2,LEN(A2)-4)&"****" |
| 3 | 9876543210 | =LEFT(A3,LEN(A3)-4)&"****" |
=LEFT(A2, LEN(A2)-4) & "****"Result: 123456****, 987654**** (shows all but last 4 digits)
Need help applying this? Try this in Numstro Builder
COMMON MISTAKES & EDGE CASES
- LEN counts ALL characters including spaces, punctuation, and line breaks
- Empty cells return 0 length
- Numbers are automatically converted to text for length calculation
- Use TRIM first if you want to exclude leading/trailing spaces
- LEN is perfect for building dynamic LEFT, RIGHT, or MID formulas
RELATED FUNCTIONS
- LEFT and RIGHT — extract characters (often combined with LEN)
- MID — extract characters from the middle
- TRIM — remove extra spaces before measuring length
- SUBSTITUTE — replace characters (changes length)
FURTHER READING
Official Microsoft documentation:
LEN — Microsoft Support