Count characters with LEN
LEN returns the number of characters in a cell, including spaces, so comparing it with the expected length finds malformed entries.
=IF(LEN(A2)=10,"OK","Check")The sheet: Column A holds 10-character product codes. You want to flag any code that is shorter or longer.
How it works, step by step
- 01LEN(A2) counts every character in A2, including spaces and punctuation.
- 02=10 tests whether the count equals the expected length.
- 03IF labels the row OK when it matches and Check when it does not.
The same logic in four tools
=IF(LEN(A2)=10,"OK","Check")=IF(LEN(A2)=10,"OK","Check")SELECT code,
CASE WHEN LENGTH(code) = 10 THEN 'OK' ELSE 'Check' END AS status
FROM products;import numpy as np
df["status"] = np.where(df["code"].str.len() == 10, "OK", "Check")Mistakes that break it
- !Leading and trailing spaces count. Use LEN(TRIM(A2)) when stray spaces are likely.
- !A number is measured by its stored value, not by how it is formatted, and leading zeros that were dropped are gone.
- !An empty cell has a length of 0.
Common questions
- How do I count a specific character?
- Compare the length before and after removing it: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")).
- Does LEN count line breaks?
- Yes, a line break counts as one character.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.