LEN: Counting Characters

About this lesson

LEN returns the number of characters in a piece of text. It counts spaces and punctuation the same as letters.

By the end you can

count the characters in a cell with LEN, spaces included.
use LEN to check a code is the right length.

Practises LEN TRIM

By Ali, Founder, Excel Lessons

The idea

One argument, one number back. Give it a postcode of eight characters and it returns 8. The space in the middle is one of them.

What makes it useful is what it finds. A value pasted in from another system often arrives with spaces in front of it or behind it. Nothing on screen shows them. That value is not equal to the one you typed. So a lookup returns #N/A and a comparison returns FALSE. The cell looks completely normal while you check the formula again and again. LEN is the two-second test. If the count is larger than what you can see, the difference is spaces. Do that before you debug anything else about text that will not match.

The mistake to watch for

Trusting what a cell looks like. A value pasted from another system arrives with spaces in front of it or behind it. Nothing on screen shows them. The value no longer equals the one you typed. The lookup returns #N/A and the comparison returns FALSE. Run LEN over text that will not match before you check anything else. A count larger than what you can see is the answer.

Where this comes up again

TRIM: The Invisible Problem Text: Building and Cleaning
Building and Cleaning: Putting It Together Text: Building and Cleaning
Why VLOOKUP Returns #N/A Errors and Robust Formulas
UPPER, LOWER and PROPER Text: Building and Cleaning

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

In `C2`, type:

=LEN(B2)

That counts the characters in the postcode.

Row	A	B	C
1	Field	Entry	Length
2	Postcode	SW1A 2AA
3	Account	4471
4	Surname	Okonkwo
5	Reference	GB-2024-0031
6	Note
7
8	Field limit	10

Every step

1

In C2, type: =LEN(B2). That counts the characters in the postcode.

Hint. One argument, one cell.
2

B3 came from another system and looks like a 4-digit account number. Read =LEN(B3) and say what it returns.

Hint. Look at where the text starts in the cell.
3

The field limit is in B8. In C6, test whether the length of B5 is within the limit. Shape: =LEN(cell)<=cell. The answer is TRUE or FALSE.

Hint. Length, then a comparison against B8.
4

B6 is empty. Read =LEN(B6) and say what it returns.

Hint. Nothing has a length too.