What LEN Does
The LEN function returns the number of characters in a text string, including spaces, punctuation, and special characters. It is commonly used for data validation, enforcing character limits, and spotting hidden whitespace that might be causing lookup failures.
Syntax
=LEN(text)
| Parameter | Description |
|---|---|
| text | The string or cell reference whose length you want to measure. |
LEN counts every character — letters, digits, spaces, and symbols all add to the total.
Basic Examples
Count characters in a cell
| A | |
|---|---|
| 1 | Google Sheets |
=LEN(A1)
Result: 13
The string has 13 characters including the space.
Check if a value exceeds a limit
Suppose you are writing product descriptions that must stay under 150 characters:
| A | B | |
|---|---|---|
| 1 | Description | Too Long? |
| 2 | Compact wireless mouse with ergonomic grip | =IF(LEN(A2)>150, "Yes", "No") |
Result in B2: No (the description is 43 characters)
Spot hidden spaces
| A | B | |
|---|---|---|
| 1 | apple | =LEN(A1) |
| 2 | apple | =LEN(A2) |
Results:
| B |
|---|
| 5 |
| 7 |
Row 2 returns 7 instead of 5, revealing two trailing spaces. This is a quick way to diagnose why a VLOOKUP or MATCH might be failing.
Advanced Examples
Count words in a cell
There is no built-in WORDCOUNT function in Google Sheets, but you can approximate one by counting spaces:
=LEN(TRIM(A1)) - LEN(SUBSTITUTE(TRIM(A1), " ", "")) + 1
This works by trimming extra spaces first, then subtracting the length without spaces from the length with spaces. The difference equals the number of spaces, and adding 1 gives the word count. For A1 = "The quick brown fox", the result is 4.
Find cells with only whitespace
Sometimes a cell looks empty but contains spaces or tabs. To flag those:
=IF(AND(LEN(A1) > 0, LEN(TRIM(A1)) = 0), "Whitespace only", "OK")
If the raw length is greater than zero but the trimmed length is zero, the cell contains nothing but whitespace. This is handy when cleaning imported datasets.
Common Mistakes
- Expecting LEN to ignore spaces. LEN counts everything, including leading, trailing, and internal spaces. If you want the length without spaces, use
=LEN(SUBSTITUTE(A1, " ", "")). - Using LEN on a number without realizing the format matters.
LEN(12345)returns5, which is correct. But if a cell displays$12,345.00due to formatting,LENstill returns5because the underlying value is the number12345, not the formatted string. To measure the displayed text, convert first:=LEN(TEXT(A1, "$#,##0.00")). - Confusing LEN with LENB. In Google Sheets, LEN and LENB behave identically for most Western text. However, LENB counts bytes and may differ for certain multibyte characters in other spreadsheet applications. In Google Sheets specifically, stick with LEN.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English and the formula is generated for you. Install SheetAI to try it free.