LEN returns the number of characters in a text string, including letters, numbers, spaces, and punctuation. Use it to measure text length, validate entries, and combine with other functions for cleaning and analysis.
=LEN(text)What does LEN do?
LEN is a Text function that tells you exactly how many characters are in a piece of text. It counts everything: letters, digits, spaces, punctuation, and even invisible characters like non-breaking spaces. The result is a number, so you can use it in calculations, comparisons, and logical tests.
Why does character count matter? Many everyday Excel tasks depend on text length: checking that a product code has the right number of digits, making sure a name fits a form field, trimming extra spaces, or extracting part of a string. LEN gives you a quick, reliable measurement without manually counting.
Imagine you receive a list of customer IDs that should all be 8 characters long. Instead of eyeballing each one, you can use LEN to flag any ID that is too short or too long. The same idea applies to phone numbers, postal codes, SKUs, and any fixed-format data.
When to use LEN
- Validate that codes, IDs, or phone numbers have the expected length.
- Find and remove extra spaces by comparing LEN with and without TRIM.
- Check if text fits within a character limit for forms or databases.
- Extract substrings by combining LEN with LEFT, RIGHT, or MID.
- Count characters in descriptions for SEO or social media limits.
- Detect empty-looking cells that actually contain spaces.
LEN syntax
=LEN(text)| Argument | Required? | What to enter |
|---|---|---|
text | Required | The text string or cell reference you want to measure. It can be a literal text in quotes, a cell containing text, or a formula that returns text. |
What it returns: A whole number representing the count of characters in the supplied text, including spaces and punctuation.
LEN examples
1 Count characters in a product code
The goal is to check the length of each product code in column A. The formula =LEN(A2) counts every character in cell A2. It returns 7 for "AB-1234" because there are two letters, one hyphen, and four digits. Copy it down to see the length of each code.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product Code | |||
| 2 | AB-1234 | 7 | ||
| 3 | XY-5678 | |||
| 4 | PQ-9012 | |||
| 5 | MN-3456 | |||
| 6 | RS-7890 | |||
| 7 | TU-2345 | |||
| 8 | VW-6789 | |||
| 9 | YZ-0123 | |||
| 10 | BC-4567 | |||
| 11 |
2 Check if customer IDs have the correct length
Customer IDs should be exactly 8 characters. The formula =LEN(B2) returns the length of the ID in B2. For "CUST1001", it returns 8, which is correct. You can compare the result to 8 to flag invalid IDs.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Customer | Customer ID | |||
| 2 | Acme Corp | CUST1001 | 8 | ||
| 3 | Beta Ltd | CUST1002 | |||
| 4 | Gamma Inc | CUST100 | |||
| 5 | Delta Co | CUST1004 | |||
| 6 | Epsilon LLC | CUST10055 | |||
| 7 | Zeta GmbH | CUST1007 | |||
| 8 | Eta SA | CUST1008 | |||
| 9 | Theta PLC | CUST1009 | |||
| 10 | Iota BV | CUST1010 | |||
| 11 |
3 Measure the length of a full name
You want to know how many characters are in each employee's full name. The formula =LEN(C2) counts all characters in C2, including the space between first and last name. For "John Smith", it returns 10 (4 letters + 1 space + 5 letters).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Employee ID | First Name | Full Name | |||
| 2 | 101 | John | John Smith | 10 | ||
| 3 | 102 | Mary | Mary Johnson | |||
| 4 | 103 | Peter | Peter Brown | |||
| 5 | 104 | Linda | Linda Davis | |||
| 6 | 105 | James | James Wilson | |||
| 7 | 106 | Patricia | Patricia Moore | |||
| 8 | 107 | Robert | Robert Taylor | |||
| 9 | 108 | Barbara | Barbara Anderson | |||
| 10 | 109 | Michael | Michael Thomas | |||
| 11 |
4 Find extra spaces by comparing LEN and TRIM
Some cells may contain leading or trailing spaces. The formula =LEN(A2)-LEN(TRIM(A2)) calculates how many extra spaces are in A2. TRIM removes leading, trailing, and repeated internal spaces. For " North ", LEN returns 9 and LEN(TRIM) returns 5, so the difference is 4.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Region | |||
| 2 | North | 4 | ||
| 3 | South | |||
| 4 | East | |||
| 5 | West | |||
| 6 | Central | |||
| 7 | Northwest | |||
| 8 | Southeast | |||
| 9 | Southwest | |||
| 10 | Northeast | |||
| 11 |
Common LEN errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #VALUE! | The text argument refers to a cell that contains an error value, or you passed a value that Excel cannot interpret as text. | Check the referenced cell for errors and correct them. Ensure the argument is text or a cell reference; numbers are fine because LEN converts them to text. |
| Wrong count due to extra spaces | LEN counts every space, including leading, trailing, and double spaces, so the result may be larger than expected. | Use =LEN(TRIM(A1)) to ignore extra spaces, or clean the data first. |
| Counting non-printing characters | Characters like non-breaking spaces (CHAR(160)) or line breaks are counted by LEN but may not be visible. | Use =LEN(SUBSTITUTE(A1,CHAR(160),"")) to exclude specific characters, or clean the text with CLEAN and TRIM. |
| Formula returns a number but you expecte | LEN always returns a number, not text. If you need the length as text, you must convert it. | Use =TEXT(LEN(A1),"0") if you need the result as text, or simply use the number in calculations. |
Tips for using LEN
- Use LEN with TRIM to measure text without extra spaces:
=LEN(TRIM(A1)). - Combine LEN with LEFT, RIGHT, or MID to extract parts of a string based on its length.
- To count specific characters, use
=LEN(A1)-LEN(SUBSTITUTE(A1,"a","")). - LEN counts numbers as characters too, so
=LEN(12345)returns 5. - Use LEN in data validation formulas to enforce character limits.
- Remember that LEN counts spaces, so "Hello World" has 11 characters, not 10.
Frequently asked questions
Does LEN count spaces?
Yes, LEN counts every character, including spaces, punctuation, and special characters. If you want to ignore extra spaces, use =LEN(TRIM(A1)).
Can LEN count characters in a number?
Yes, LEN converts numbers to text and counts the digits. For example, =LEN(12345) returns 5. It does not count formatting like commas or currency symbols.
How do I count how many times a specific character appears?
Use =LEN(A1)-LEN(SUBSTITUTE(A1,"x","")), replacing "x" with the character you want to count.
Why does LEN return a different number than I see?
The cell may contain hidden characters such as non-breaking spaces or line breaks. Use =LEN(CLEAN(A1)) or =LEN(TRIM(A1)) to see the difference.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. LEN reference