Function guides · Text
LEN function
Returns the number of characters in text.
Syntax
=LEN(text)
| Argument | What it is |
|---|---|
text | The text or cell to measure. Numbers are measured as if they were text. |
What LEN does
LEN returns the number of characters in a piece of text, spaces included. LEN(“Mei Tanaka”) is 10.
It is the function for checking and measuring. Is an ID the right length? Does a cell hold extra spaces? How far from the end of the text is a separator? Each is a LEN question.
It reads the stored value, not the display. A number formatted as currency is measured as the plain digits, and a date is measured as its five-digit serial number.
On its own it is plain. In combination with FIND, RIGHT, SUBSTITUTE and TRIM it does a lot of work.
LEN is also the engine behind a few common formulas. Taking the part of a name after the first space uses RIGHT with LEN minus the position of the space. Counting words, or counting how often a character appears, compares the length before and after removing something with SUBSTITUTE. Padding a number to a fixed width checks LEN and adds zeros until it matches.
For data validation, the Text length option in the Data Validation dialog uses the same idea without a formula: it blocks entries that are too short or too long as they are typed.
LEN examples
1. Validate the length of an ID
| A | B | |
|---|---|---|
| 1 | ID | Valid? |
| 2 | E-104 | Check |
Every employee ID must be 8 characters. LEN returns the length so a comparison can flag the wrong ones. "E-104" is only 5 characters long.
2. Spot hidden spaces
| A | B | |
|---|---|---|
| 1 | Name | |
| 2 | Kona | 5 |
The name looks like four letters, but LEN says five, so there is a trailing space. Comparing LEN with the length of the TRIM result is a quick audit for dirty text.
3. Count how many times a letter appears
| A | B | |
|---|---|---|
| 1 | Word | |
| 2 | banana | 3 |
Remove every "a" with SUBSTITUTE and compare lengths. The difference is the number of removed characters. "banana" is 6 letters and "bnn" is 3, so there were 3 a's.
Common LEN mistakes
- Forgetting that spaces count. A trailing space adds one. LEN("Kona ") is 5. TRIM first if you want the length of the visible text.
- Measuring numbers with formatting. LEN looks at the stored value, not the display. A cell showing $1,200.00 has the length of 1200, which is 4.
- Dates measured as numbers. A date's serial number is 5 digits, so LEN of a date returns 5.
- Case sensitivity in counting letters. SUBSTITUTE is case sensitive, so counting "a" misses "A". Wrap the text in LOWER.
LEN on Excel job tests
LEN is usually a building block. Typical uses are flagging IDs that are not 8 characters long, finding cells with extra spaces by comparing LEN(A2) with LEN(TRIM(A2)), and supplying the length inside RIGHT, MID or SUBSTITUTE formulas.
If the task says a code must be exactly 6 characters, it may want a data validation rule with the Text length option as well as, or instead of, a flagging formula. Read the wording for "prevent" versus "flag".
When checking for hidden spaces, test a leading space, a trailing space and a doubled space in the middle.
Related functions
LEN questions
- Does LEN count spaces?
- Yes. Every character counts, including spaces.
- How do I count words in a cell?
=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1for text with single spaces between words.- Does LEN work on numbers?
- Yes, it counts the digits of the stored value, not the displayed format.
- What is LEN of an empty cell?
- Zero.
Practice LEN
Try it in a working grid: the free lesson LEN and TRIM: measure and clean (module 9 of Everyday Excel) has checked LEN exercises.
Practice this function