The LEN function in Excel
LEN returns the number of characters in a text value, counting letters, digits, punctuation and spaces. It's the quick way to check that IDs have the right length or that a headline fits a character limit. Combined with SUBSTITUTE, it can also count how many times a character appears, a classic Excel test question.
Syntax
=LEN(text)
| text | The text or cell to measure, like A2. |
|---|
Try it yourself
This is a live spreadsheet. Type the formula and press Enter.
Text messages must stay within a character limit. In C2, use LEN to count the characters in the SMS text in B2.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Characters | ||||||
| 2 | 24h only: 30% off all shoes. Use code RUN30 at checkout. | ||||||
| 3 | Your favourite trainers are back in stock! | ||||||
| 4 | Your order ships tomorrow. Track it in the app. | ||||||
| 5 | You've earned 500 points. Redeem them before 31 May. | ||||||
| 6 | |||||||
| 7 | |||||||
| 8 |
Tip: LEN takes a single argument: the cell you want to measure.
Examples
=LEN(A2)→ 9Counts the characters in the product code, including the hyphen. Useful for spotting codes that are too short or too long.
=IF(LEN(B3)>40,"Too long","OK")→ "Too long"Checks the headline against a 40-character limit. This one is longer, so it's flagged.
=LEN(C2)-LEN(SUBSTITUTE(C2,",",""))+1→ 3Removing the commas makes the text shorter by one for each comma. Add 1 and you get the number of items in the list.
Example data
| 1 | Code | Ad headline | Colours |
|---|---|---|---|
| 2 | SKU-10482 | Spring sale starts Friday | red, blue, green |
| 3 | SKU-993 | Free delivery on every order over $50 this weekend only | black |
Common mistakes
Forgetting that spaces count
LEN counts every space, including invisible ones at the start or end. If lengths look one or two too long, measure the cleaned text:
LEN(TRIM(A2)).Measuring numbers and dates
LEN measures the stored value, not what you see. A date shows as 3/15/2025 but is stored as a 5-digit serial number, so LEN returns 5. Use
LEN(TEXT(A2,"m/d/yyyy"))to measure the display.Expecting a word count
LEN counts characters, not words. To count words, count the spaces in the trimmed text and add 1 (see the FAQ below).
FAQ
How do I count a specific character in a cell?+
Subtract the length without that character from the full length: =LEN(A2)-LEN(SUBSTITUTE(A2,"-","")) counts the hyphens.
How do I count words in a cell?+
Use =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1. TRIM makes sure there's exactly one space between words. An empty cell would show 1, so wrap it in IF if blanks are possible.
Does LEN count spaces?+
Yes. Every space counts as one character, wherever it is in the text.
Lesson
Master LEN step by step: LEFT, RIGHT, MID and LEN
Split product codes and IDs into their parts by taking characters from the start, end or middle of the text.