Excel LEN Function — Count Characters in Excel (with Examples)

What does LEN do?

LEN returns the total number of characters in a text string, counting everything — letters, numbers, spaces, punctuation, and symbols all count as one character each. Use it to validate data lengths, detect hidden spaces, check field constraints, and spot anomalies in imported data.

Syntax

=LEN(text)

Argument	Description
text required	The text string or cell to measure. Numbers are converted to text before counting.

5 real examples

Example 1

Check postcode length

=IF(LEN(A2)<6, "Too short", "OK")

Example 2

Detect hidden spaces

=IF(LEN(A2)<>LEN(TRIM(A2)), "Has spaces", "Clean")

If LEN differs from LEN(TRIM), extra spaces are hiding in the cell.

Example 3

Flag overlong entries

=IF(LEN(A2)>140, "Too long", "OK")

Example 4

Count words in a sentence

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

Count spaces (words minus 1) then add 1 for the first word.

Example 5

Validate NI number format (9 chars)

=AND(LEN(A2)=9, LEFT(A2,2)<>"D")

FAQ

Does LEN count spaces?

Yes — every space counts as one character. "Hello World" has 11 characters including the space.

Can LEN work on numbers?

Yes, LEN converts numbers to text first. LEN(1234) returns 4. LEN(1234.5) returns 6.

What does LEN return for an empty cell?

0. Use =LEN(A2)=0 to check for empty cells — same as ISBLANK but works on cells containing empty strings too.

Is LEN case sensitive?

No — LEN just counts characters regardless of case.

LEN for data validation and quality checking

LEN is the fastest way to spot data quality issues in a column. After importing data, a quick =MAX(LEN(A:A)) tells you the longest entry — useful for checking whether anything exceeds a field limit. =MIN(LEN(A2:A100)) (entered as an array formula) finds the shortest — which might reveal entries that are too short to be valid.

LEN is also the foundation of the hidden-space detector. If =LEN(A2)<>LEN(TRIM(A2)) returns TRUE, there are extra spaces in the cell. You cannot see them by looking, but they will break VLOOKUP and COUNTIF. LEN makes the invisible visible.

For word count, the formula =LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1 counts spaces (which is words minus 1), then adds 1. The TRIM ensures that extra spaces do not inflate the count. This is the standard technique for word-count validation in text fields.

💡 LEN with conditional formatting

Apply conditional formatting with the rule =LEN(A1)<>9 to highlight any cells in a column that do not have exactly 9 characters — instantly flags invalid NI numbers, postcodes, or reference codes across an entire column.

Practise data validation formulas

ExcelPro has LEN, TRIM, LEFT and SUBSTITUTE exercises across all tracks. Free to start.

Try text exercises →

Related formulas

TRIM LEFT RIGHT SUBSTITUTE IF MID

The Excel LEN Functionexplained simply