LEN Function in Google Sheets
Use LEN to count how many characters are in a cell — handy for validation, trimming checks and text limits.
=LEN(A2)
Returns the number of characters in A2, including spaces and punctuation.
How it works
LEN returns the total character count of the text in a cell — letters, digits, spaces and symbols all count. Numbers are measured by their displayed digits. LEN is most useful inside bigger formulas: to flag entries that are too long, to count words, or to check for hidden trailing spaces by comparing LEN before and after TRIM.
Variations
Count words in a cell
=IF(A2="",0,LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1)
Counts spaces between words and adds one.
Length without spaces
=LEN(SUBSTITUTE(A2," ",""))
Removes spaces first, then counts.
Flag entries over a limit
=IF(LEN(A2)>280,"Too long","OK")
Useful for character-limited fields.
Examples
| Scenario | Formula |
|---|---|
| Check a code is exactly 6 characters | =IF(LEN(A2)=6,"Valid","Wrong length") |
| Detect hidden trailing spaces | =LEN(A2)-LEN(TRIM(A2)) |
FAQ
Does LEN count spaces?
Yes. LEN counts every character including leading, trailing and in-between spaces. Use LEN(TRIM(A2)) to ignore edge spaces.
How do I count characters across a range?
Wrap it in a sum: =SUMPRODUCT(LEN(A2:A100)) totals the characters in every cell.
Why does LEN return a bigger number than expected?
There are probably extra spaces or non-printing characters. Clean the cell with TRIM and CLEAN first.
Related formulas
- #N/A Error in Google Sheets — Why Lookups Return It and How to Fix
- #NAME? Error in Google Sheets — Fix Unknown Names
- #NUM! Error in Google Sheets — Causes and Fixes
- #REF! Error in Google Sheets — What It Means and How to Fix It
- #VALUE! Error in Google Sheets — Causes and Fixes
- COUNTIF vs COUNTIFS in Google Sheets — When to Use Each
- FILTER vs QUERY in Google Sheets — Which Should You Use
- IF vs IFS in Google Sheets — When to Use Each