Start for free

All Excel functions

LEN in Excel: how to use it

LEN counts how many characters a cell contains, spaces and punctuation included.

Try the first exercises for freeIn the lesson LEN and TRIM

What does LEN do?

LEN counts how many characters there are in a cell: letters, digits, spaces and punctuation. You get one number back.

On its own that doesn't seem very useful, but LEN helps you track down mistakes: a code that's too short, a hidden space, or a text that's too long for a field. Together with LEFT, RIGHT and MID, you can also cut out pieces of varying length.

When do you use LEN?

  • Checking that every branch code has four characters: =IF(LEN(A2)=4,"OK","Check").
  • Tracking down a hidden space: =LEN(A2)-LEN(TRIM(A2)).
  • Checking that a text isn't too long, such as a text message: =LEN(B2)<=160.
  • Everything except the first three characters: =RIGHT(A2,LEN(A2)-3).

Syntax

The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.

=LEN(text)

Filled in: =LEN(A2)

This is how to read the formula, part by part:

PartWhat it meansIn the example
=Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text.=
LENThe name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself.LEN
( )Straight after the name come the brackets. Inside them is what the function needs to work: the arguments.( )
textThe cell or text whose characters you count.A2

LEN step by step

C1 contains =LEN(A2). U, K, a hyphen, four digits, another hyphen and two digits: 10 characters in all. Change the numbers or the formula and see what happens.

C1
A
B
C
D
E
F
G
H
1
Code
10
2
UK-2026-07
3
4
5
6
7
8
9
10
Ready

This is how to build the formula yourself:

  1. Click C1 and type LEN with a bracket.=LEN(
  2. Click the cell with the text, A2.=LEN(A2
  3. Close the bracket and press Enter: the code has 10 characters, including the hyphens.=LEN(A2)

More examples

Checking a code

B2 contains =IF(LEN(A2)=4,"OK","Check"). This branch code has five characters instead of four. Type 9000 in A2, and you see OK.

B2
A
B
C
D
E
F
G
H
1
Branch code
Check
2
90000
Check
3
4
5
6
7
8
9
10
Ready

Everything except the start

C1 contains =RIGHT(A2,LEN(A2)-3). LEN gives 10. Take away the 3 characters of UK-, and RIGHT takes the 7 characters that are left.

C1
A
B
C
D
E
F
G
H
1
Code
2026-07
2
UK-2026-07
3
4
5
6
7
8
9
10
Ready

Spaces count too

C1 contains =LEN(A2). Mark Hall has 9 characters, but the cell gives 10: there's another space at the end.

C1
A
B
C
D
E
F
G
H
1
Name
10
2
Mark Hall
3
4
5
6
7
8
9
10
Ready

Tracking down hidden spaces

Two names look the same, but VLOOKUP can't find them? Compare their length. =LEN(A2)-LEN(TRIM(A2)) shows how many extra spaces a cell contains.

Checking whether a code is right

A customer number often has a fixed length. =IF(LEN(A2)=8,"OK","Check") shows straight away which numbers don't have eight characters.

Where it often goes wrong

  • LEN also counts the characters of a number: =LEN(1500) gives 4. Formatting, such as a thousands separator, doesn't count.
  • An empty cell gives 0, a cell with just a space gives 1.
Want to try it yourself?
Practise this in the lesson LEN and TRIM. The first exercises are free.

Try the first exercises for free

Frequently asked questions

How do I count the words in a cell?

=LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))+1 counts the spaces between the words and adds 1.

Read next

LEFT, RIGHT and MIDTRIMUPPER, LOWER and PROPERSEARCH and FINDJoining text