SheetsdrillLEN
LEN(text) returns the number of characters in a cell, counting letters, digits, spaces and punctuation. It is used to validate codes and IDs of a fixed length, to find cells with stray spaces, and together with SUBSTITUTE to count words or occurrences.
Syntax
=LEN(text)| Part | What it is |
|---|---|
text | The cell or string to measure. Numbers are measured as their displayed digits, dates as their serial number. |
Examples
=LEN("Google Sheets")Result: 13
=FILTER(A2:A100, LEN(A2:A100)>8)Result: Every code longer than eight characters
=LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+1Result: The number of words in A2 (for single-spaced text)
Common mistakes
- LEN on a date returns 5 (the serial number); wrap in TEXT with a format first.
- A cell that looks like 5 characters but measures 6 has a trailing space or a non-breaking space; TRIM removes normal spaces, CLEAN and SUBSTITUTE(A2, CHAR(160), "") handle the rest.
- LEN on an empty cell returns 0, so a check like LEN(A2)>0 is a handy not-blank test.
Practice LEN
These drills check your answer on live data, free, no account needed.
- Codes that are too longCount the product codes in column A that have more characters than the limit in E1. Use ARRAYFORMULA with LEN. Answer in E2.
Questions
How do I count characters in a whole column?
=ARRAYFORMULA(LEN(A2:A100)) gives one count per row, and =SUMPRODUCT(LEN(A2:A100)) gives the total.
How do I count how many times a letter appears?
=LEN(A2)-LEN(SUBSTITUTE(A2, "a", "")) counts lowercase a; SUBSTITUTE is case sensitive.
Related
- TRIMHow TRIM in Google Sheets removes leading, trailing and repeated spaces, how to clean a whole column, and how to clear non-breaking spaces and line breaks.
- LEFT, RIGHT and MIDHow LEFT, RIGHT and MID in Google Sheets extract characters from the start, end or middle of a cell, with FIND for variable positions, and common errors.
- SUBSTITUTEHow SUBSTITUTE in Google Sheets replaces one piece of text with another, removes characters, targets one occurrence, and how it differs from REGEXREPLACE.
- REGEXMATCH, REGEXEXTRACT and REGEXREPLACEHow the regex functions in Google Sheets work: test a pattern with REGEXMATCH, pull text out with REGEXEXTRACT and clean it with REGEXREPLACE, with examples.