SheetSense

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.

fx
=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

  1. 01LEN(A2) counts every character in A2, including spaces and punctuation.
  2. 02=10 tests whether the count equals the expected length.
  3. 03IF labels the row OK when it matches and Check when it does not.

The same logic in four tools

Excel
=IF(LEN(A2)=10,"OK","Check")
Google Sheets
=IF(LEN(A2)=10,"OK","Check")
SQL
SELECT code,
       CASE WHEN LENGTH(code) = 10 THEN 'OK' ELSE 'Check' END AS status
FROM products;
Python (pandas)
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.