What it does
LEN takes a single argument and counts the characters in it. Letters, digits, spaces, dots and hyphens each count as one character.
On its own it is most useful for data checks: together with IF it tells you whether a barcode really has 13 digits or a description stays within a character limit. It is also a common helper for other text functions; when RIGHT or MID needs a part of varying length, LEN calculates the number of characters.
The usual way to count how many times a character appears is to subtract the length of the text without that character from the full length: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")).
Syntax
=LEN(text)=UZUNLUK(metin)Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
text | Required | The text or cell whose characters are counted. An empty cell returns 0. For numbers, the stored value is counted, not the formatting: 1250 shown as 1,250.00 returns 4. |
Examples
1Check barcode lengths
Every barcode should have 13 digits. Column C counts the length of each barcode and column D flags the ones that are not 13.
=LEN(B2)| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Barcode | Length | Status |
| 2 | Copper cable | 5012345678900 | 13 | OK |
| 3 | Grounded socket | 501234567891 | 12 | Check |
| 4 | Circuit breaker | 5012345678924 | 13 | OK |
| 5 | Cable duct | 50123456789311 | 14 | Check |
| 6 | Light switch | 5012345678948 | 13 | OK |
- C2 (filled down to C6)
=LEN(B2) - D2 (filled down to D6)
=IF(C2=13,"OK","Check")
The grounded socket's barcode has 12 characters and the cable duct's has 14; both show Check in column D.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Catch invisible spaces
The names were copied from another program and contain extra spaces that you cannot see. Column B returns the difference between the length of the text and the length of its trimmed version.
=LEN(A2)-LEN(TRIM(A2))| A | B | |
|---|---|---|
| 1 | Full Name | Extra Spaces |
| 2 | Emma Brown | 0 |
| 3 | Lucas Gray | 1 |
| 4 | Ava Thompson | 1 |
| 5 | Noah Clark | 2 |
- B2 (filled down to B5)
=LEN(A2)-LEN(TRIM(A2))
The first row is clean: 0. The others have 1, 1 and 2 extra spaces; clean them with TRIM before matching them against another list.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Count the products listed in a cell
The product codes of each order are written in one cell, separated by commas. The number of commas is the length of the text minus the length without commas; the product count is one more.
=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1| A | B | C | |
|---|---|---|---|
| 1 | Order | Product Codes | Products |
| 2 | S-1001 | KBL-1525,PRZ-0210,SGT-0016 | 3 |
| 3 | S-1002 | KNL-2040 | 1 |
| 4 | S-1003 | PRZ-0210,ANH-0300 | 2 |
| 5 | S-1004 | KBL-1525,KBL-1540,KNL-2040,SGT-0016 | 4 |
- C2 (filled down to C5)
=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1
S-1001 has 3 products, the single-item S-1002 has 1 and S-1004 has 4.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- Larger than expected
Cause: The text has extra spaces at the start, at the end or between words. They are invisible but they count.
Fix: Compare with
=LEN(TRIM(A2)); if they differ, clean the data with TRIM.- Missing digits
Cause: For numbers, the stored value is counted, not what you see. Leading zeros added by a number format such as 00125 are not counted, so the result is 3.
Fix: To count what is shown, convert it to text first:
=LEN(TEXT(A2,"00000")).
Good to know
- To find codes of the wrong length in a column, add a LEN column and filter or sort it; the faulty rows gather at the top or bottom.
- Store long barcodes and reference numbers as text, not numbers; Excel turns the digits after the 15th into zeros.
In Nexell
Get Nexell to do it from a sentence
Write "Check" in a Status column for products whose barcode is not 13 digits long
Convert to tableBring your product list into the tool and type this. Nexell adds the Status column with real Excel formulas and the preview shows which rows are flagged; in the downloaded file, the flag disappears once you fix the barcode.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I count the words in a cell?
Count the spaces and add one: =LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1. TRIM removes extra spaces, so double spaces do not spoil the count. An empty cell returns 1; avoid that with =IF(A2="",0,…).
How do I count how many times a letter appears in a cell?
Subtract the length of the text without that letter from the full length: =LEN(A2)-LEN(SUBSTITUTE(A2,"a","")). SUBSTITUTE is case-sensitive, so "A" is not counted.