Excel LEN Function: count characters in a cell

LEN tells you how many characters a cell holds — letters, digits, spaces, all of them. It is small, but it catches problems nothing else shows.

Steps

1. Text in column A.
2. Click B1. Type =LEN(A1). Enter. Drag down.

“Ana Roy” → 7 (the space counts).

Job 1: find hidden spaces

“Raj ” shows 4, not 3. That trailing space is why the lookup failed. Fix with TRIM.

Job 2: check codes are the right length

Indian PIN codes are 6 digits:

=IF(LEN(A1)=6, "OK", "Check")

Or stop wrong ones being typed: Data Validation › Text length › equal to 6.

Job 3: flag text that is too long

Product titles over 60 characters:

=IF(LEN(A1)>60, "Too long", "")

Job 4: count words

=LEN(TRIM(A1)) - LEN(SUBSTITUTE(TRIM(A1), " ", "")) + 1

Characters minus characters-without-spaces gives the number of spaces; plus one is the word count.

Check it worked

=LEN("") is 0. =LEN(" ") is 1. A number like 560001 counts 6 whether it is stored as number or text.

Common mistake

LEN counts what is in the cell, not what is displayed. A date shows as “29-Sep-2026” but LEN gives 5 — the underlying number 46294.