Available in:All versions (Works in Excel 2007, every later version, Microsoft 365 and Excel for the web)Examples need Excel 2021 or laterCategory:Text

LEN

LENGTE in Dutch Excel

Counts the characters in a text, including spaces, punctuation and characters you cannot see on screen.

Difficulty rating

Last updated: 13 October 2026

See the worked examples

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Entry no.	Journal	Length
2	25-0412	VRK	77=LEN(A2)=LEN(A2)
3	25-041	MEM
4	25-0414	INK
5	25-0415	BNK

Note: The entry numbers are stored as text.

Result: 7

=LEN(A2)

=LEN(A2)

What LEN does and when to use it

What it does: it counts the characters in a text, spaces and invisible characters included, for example to check that an entry number has six characters.

When to use it: when a lookup fails on a value that looks identical (a hidden space), when you check fixed-length codes such as barcodes, or when LEFT, RIGHT or MID need a length. To stop wrong lengths at entry, Data Validation with a text length rule works better.

How Excel evaluates it

Excel reads text. If it is a number or a date, Excel first converts it to text the way it is stored. Then it counts every character from the first to the last, and returns that count as a number.

Watch out: LEN reads the stored value, not what the cell shows: 417 formatted to show 000417 is read as 417. The cell format has no influence at all on the result.

Mental model

A tape measure laid along the stored value, not along what the screen shows: it measures the gaps as well as the letters.

Syntax

Official LEN documentation

LEN(text)

LEN(text)

Arguments
Argument	Type	Meaning
`text`	Required	The text whose characters you want to count.

Return value:A number: how many characters the text contains

Formulas use commas (,) between arguments, as in English Excel. Formulas use semicolons (;) between arguments, as in Dutch Excel. Formulas use commas (,) between arguments, as in English Excel; Dutch Excel uses semicolons (;).

Worked examples

Download example workbook(Excel file, 5 sheets, 16 KB)

Beginner

Check that entry numbers have the right length

You are testing journal entries from the XAF auditfile of Veldhuis Installatie BV. Every entry number follows the pattern 25-0412: two digits for the year, a hyphen and four digits, so 7 characters.

Manual entries were keyed in by hand, and one number is probably incomplete. You add a column that counts the characters of each entry number.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Entry no.	Journal	Length
2	25-0412	VRK	77=LEN(A2)=LEN(A2)
3	25-041	MEM
4	25-0414	INK
5	25-0415	BNK
6

Formula cell (C2)InputNote: The entry numbers are stored as text.

Formula in C2

=LEN(A2)

=LEN(A2)

Result: 77

Step by step

Excel reads text: A2 holds the entry number 25-0412.
It counts the characters one by one: 2, 5, hyphen, 0, 4, 1, 2. That is 2 + 1 + 4 = 7.
LEN returns the number 7, and C2 shows 7. This entry number has the right length.
Copy C2 down to C5. C3 shows 6: 25-041 is one digit short, and that is the manual (MEM) entry to follow up with the client. C4 and C5 show 7.
Correct A3 to 25-0413 and C3 changes to 7 at once: the length is recalculated from the cell, not stored.

Watch out: A correct number with a trailing space shows 8, and a number with a missing digit plus a trailing space shows 7, which passes the check. If the numbers were keyed in by hand, measure LEN(TRIM(A2)) instead.

More on this example: why it works, 1 variation

Why it works

A valid entry number starts with the right length, and LEN turns a code that is hard to measure by eye into a single number you can scan down a column of thousands of journal lines. Because the numbers are stored as text, every character, including the hyphen, is counted.

Variations

=LEN(A2) = 7

=LEN(A2) = 7

Returns TRUE or FALSE directly, which is handy for a filter or conditional formatting.

Intermediate

Flag invoice numbers with trailing spaces

You match the invoice numbers of the receivables aging list to the general ledger, and several lookups return #N/A for invoice numbers that are visibly in both. You suspect trailing spaces in the client's export. Before cleaning anything, you want a column that shows which rows are affected, so you can report it to the client.

Sheet excerpt: C3 holds the formula
Row	A	B	C
1	Invoice no.	Relation	Extra spaces?
2	F-25-0871	D1001
3	F-25-0872	D1002	TRUETRUE=LEN(A3) <> LEN(TRIM(A3))=LEN(A3) <> LEN(TRIM(A3))
4	F-25-0873	D1003
5	F-25-0874	D1004
6

Formula cell (C3)InputMarks: extra spaceNote: A3 ends with one space and A5 with two. On screen all four invoice numbers look the same width.

Formula in C3

=LEN(A3) <> LEN(TRIM(A3))

=LEN(A3) <> LEN(TRIM(A3))

Result: TRUE

Step by step

Excel evaluates the left side: LEN(A3). A3 is F-25-0872 followed by a space: F, hyphen, 2, 5, hyphen, 0, 8, 7, 2 is 9 characters, plus the space, so 10.
It evaluates the right side, inside out. TRIM(A3) removes the trailing space and returns F-25-0872. LEN of that is 9.
It compares: 10 <> 9 is TRUE. C3 shows TRUE: this row carries a space that TRIM would remove, and that is why the match fails.
Copied to C2, F-25-0871 gives 9 on both sides and 9 <> 9 is FALSE. C5 gives 11 <> 9, TRUE. C4 is FALSE.
LEN alone would show 10 in C3, which only tells you something if you know the numbers should be 9 characters. The comparison with TRIM works for numbers of any length, because the text is measured against its own cleaned version.

Watch out: A non-breaking space at the end is not removed by TRIM, so both lengths are equal and the row shows FALSE while the lookup still fails. If a lookup fails on a row marked FALSE, test for CHAR(160).

More on this example: why it works, 1 more pitfall, 1 variation

Why it works

TRIM removes only spaces that should not be there, so any difference in length between a text and its trimmed version is exactly the number of stray spaces. LEN turns that invisible difference into a visible number, and the comparison turns the number into a flag you can filter on.

More to watch out for

The test also flags double spaces in the middle of a text. For invoice numbers that is what you want; for free text such as descriptions it may flag rows that do not break any lookup.

Variations

=LEN(A3) - LEN(TRIM(A3))

=LEN(A3) - LEN(TRIM(A3))

Returns the number of extra spaces instead of TRUE or FALSE: 1 for A3, 2 for A5.

Advanced

Count the invoices paid in one bank line

In the bank statement export, the client's bank lists the invoice numbers settled by a payment in one cell, separated by semicolons. To agree the bank lines to the subledger, you need to know how many invoices each line covers. Some lines have no invoice reference, and those cells are empty.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Bank line	Invoices	Number of invoices
2	BNK-0101	25-1101;25-1102;25-1110	33=IF(B2 = "", 0, LEN(B2) - LEN(SUBSTITUTE(B2, ";", "")) + 1)=IF(B2 = ""; 0; LEN(B2) - LEN(SUBSTITUTE(B2; ";"; "")) + 1)
3	BNK-0102	25-1120
4	BNK-0103
5	BNK-0104	25-1130;25-1131;
6

Formula cell (C2)InputNote: B5 ends with a semicolon that the bank added after the last invoice.

Formula in C2

=IF(B2 = "", 0, LEN(B2) - LEN(SUBSTITUTE(B2, ";", "")) + 1)

=IF(B2 = ""; 0; LEN(B2) - LEN(SUBSTITUTE(B2; ";"; "")) + 1)

Result: 33

Step by step

Excel evaluates the test first: B2 = "". B2 holds three invoice numbers, so the test is FALSE and IF moves on to its third argument.
LEN(B2) counts 25-1101;25-1102;25-1110: three invoice numbers of 7 characters each and two semicolons. 3 × 7 + 2 = 23.
SUBSTITUTE(B2, ";", "") removes every semicolon: 25-110125-110225-1110. LEN of that is 21.
The difference is the number of semicolons: 23 - 21 = 2. There is always one more item than there are separators, so 2 + 1 = 3. C2 shows 3.
Copied to C3, 25-1120 has no semicolon: 7 - 7 + 1 = 1. In C4, B4 is empty, the test is TRUE and IF returns 0. Without that test, the empty cell would give 0 - 0 + 1 = 1 invoice.
In C5 the result is wrong without any warning: 25-1130;25-1131; has 16 characters and 14 without semicolons, so 16 - 14 + 1 = 3, while the bank line covers 2 invoices. The trailing semicolon counts as a separator.

Watch out: A trailing or doubled separator adds a phantom item, as row 5 shows. The result looks like a normal count. If the source can produce these, strip them first, or count with the variation below.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

Excel has no function that counts how often a character occurs in a text, but LEN can measure what disappears when you remove it. Removing a one-character separator shortens the text by exactly one per occurrence, so the difference in length is the count. The IF handles the one case where the item-equals-separators-plus-one rule does not hold: an empty cell.

More to watch out for

For a separator longer than one character, such as a semicolon plus a space, divide the difference by the separator's length: (LEN(B2) - LEN(SUBSTITUTE(B2, "; ", ""))) / 2 + 1.
SUBSTITUTE is case-sensitive. That does not matter for a semicolon, but counting a letter this way would miss the other case.

Variations

=(LEN(B2) - LEN(SUBSTITUTE(B2, "25-", ""))) / 3

=(LEN(B2) - LEN(SUBSTITUTE(B2; "25-"; ""))) / 3

Counts the invoices by their prefix 25- instead of by the separators; the prefix is three characters, hence the division by 3. It gives 2 for B5 and 0 for an empty cell, with no IF needed.

MasterExcel 2021 or later (This example needs Excel 2021 or later)

Check a four-digit account number however it is stored

You map the accounts of the trial balance to the financial statement lines, and every account number must be exactly four digits, with a leading zero for the fixed assets (0800). The column is a mix:

numbers with a custom format 0000 that shows the zero
text typed with the zero
one text with a trailing space
one account number with five digits

A plain LEN check flags the good formatted numbers and passes the bad text, so you build a check that looks at the account number the way the ledger sees it.

Sheet excerpt: C2 holds the formula
Row	A	B	C
1	Account	Balance	Check
2	800800	€184,500.00€ 184.500,00	OK=LET( raw, A2, asText, IF(ISNUMBER(raw), TEXT(raw, "0000"), TRIM(raw)), IF(LEN(asText) = 4, "OK", "Check") )=LET( raw; A2; asText; IF(ISNUMBER(raw); TEXT(raw; "0000"); TRIM(raw)); IF(LEN(asText) = 4; "OK"; "Check") )
3	0810	-€62,300.00€ -62.300,00
4	13001300	€95,400.00€ 95.400,00
5	800	€12,750.00€ 12.750,00
6	8000080000	€3,200.00€ 3.200,00
7

Formula cell (C2)InputMarks: extra spaceNote: A2, A4 and A6 are numbers with the custom format 0000, so A2 shows 0800. A3 and A5 are text; A5 is 800 followed by a space.

Formula in C2

=LET(
  raw, A2,
  asText, IF(ISNUMBER(raw), TEXT(raw, "0000"), TRIM(raw)),
  IF(LEN(asText) = 4, "OK", "Check")
)

=LET(
  raw; A2;
  asText; IF(ISNUMBER(raw); TEXT(raw; "0000"); TRIM(raw));
  IF(LEN(asText) = 4; "OK"; "Check")
)

Result: OK

Step by step

raw is A2. The cell shows 0800, but it stores the number 800. LEN(A2) on its own would return 3, because LEN reads the stored value.
asText: ISNUMBER(800) is TRUE, so IF takes TEXT(raw, "0000"). The format code pads with zeros to four places and returns the text 0800.
LEN(asText) is 4. 4 = 4 is TRUE, so the last IF returns OK. C2 shows OK.
Copied to C3, 0810 is text: ISNUMBER is FALSE, TRIM leaves it at 0810, LEN is 4, OK. In C4, 1300 is a number, TEXT returns 1300, LEN 4, OK.
In C5, 800 plus a space is text of 4 characters, so a plain LEN check would pass it. Here TRIM makes it 800, LEN is 3, and C5 shows Check. The account number lacks its leading zero.
In C6, 80000 is a number. TEXT with 0000 never cuts digits off, it only pads, so the result is 80000, LEN 5, and C6 shows Check.

Watch out: The mirror-image mistake is =LEN(A2) = 4 without the conversion. It flags 0800 as wrong (the stored 800 has 3 characters) and passes 800 with a space (4 characters). Both errors look like reasonable results.

More on this example: why it works, 2 more pitfalls, 1 variation

Why it works

LEN measures the stored value, so it has to be given the value in the form the rule is about. The formula brings both storage types to the same shape first: numbers through TEXT with the same zero-padding the cell format uses, text through TRIM. Only then is a length of 4 a meaningful test.

Do not use this construction to guard data entry: Data Validation with a custom rule stops wrong numbers as they are typed, which is better than finding them later. The trade-off is that validation does not check values that were pasted or imported, and this formula does.

More to watch out for

The check tests the length, not that every character is a digit. A text such as 08A0 passes. Add ISNUMBER(--asText) to the test if letters can creep in.
A number with decimals, such as 1300.5, becomes 1301 through the format code 0000, which rounds. LEN then says OK. If decimals are possible in the source, test for them separately.

Variations

=IF(LEN(IF(ISNUMBER(A2), TEXT(A2, "0000"), TRIM(A2))) = 4, "OK", "Check")

=IF(LEN(IF(ISNUMBER(A2); TEXT(A2; "0000"); TRIM(A2))) = 4; "OK"; "Check")

The same test without LET, for Excel 2019 and older.

Common mistakes

Common mistakes
Symptom	Cause	Fix
Codes look equal but LEN differs	Two codes look the same, LEN says 6 and 7, and you cannot see the difference.	The extra character is invisible: usually a trailing space, sometimes a non-breaking space or a line break. Compare LEN(A2) with LEN(TRIM(A2)); if TRIM does not close the gap, look for CHAR(160).
4 for an amount shown as 1,250.00	LEN of an amount shown as 1,250.00 returns 4.	The stored value 1250 has 4 characters, whatever the format shows. If you need the length of what is shown, measure TEXT(A2, format) instead.
Always 5 for a date	LEN of a date returns 5, whatever the date looks like.	A date is a serial number such as 46295. LEN is the wrong tool for dates; use ISNUMBER to check that a cell holds a real date.