Detect Invisible Blank Cells That Look Filled

You run a required-field check on your avails. ISBLANK(B2) returns FALSE. COUNTA says the column is 100% populated. You ship. The platform rejects 12 rows because Territory is empty. You go back to the sheet and the cells look filled. You copy one into the formula bar. It’s a single character — a non-breaking space, char 160. Invisible, but present.

Why This Happens

Sheets treats non-breaking spaces (CHAR(160), often from Word or web paste), zero-width spaces (CHAR(8203), from rich-text paste), and plain spaces as text. None of them are “blank” by ISBLANK’s definition. COUNTA counts them. Your required-field check misses them. The ingest downstream rejects them.

The tell: a “filled” cell that LEN reports as 1 when it should be zero, or whose content is literally " " but isn’t visible.

The Detection Formula

Compare LEN vs. LEN(TRIM(CLEAN(...))):

=IF(LEN(TRIM(CLEAN(SUBSTITUTE(B2, CHAR(160), "")))) = 0, "EMPTY", "FILLED")

Walking through:

• SUBSTITUTE(B2, CHAR(160), "") — remove non-breaking spaces.
• CLEAN(...) — strip non-printable characters (CHAR 0-31, but not 160).
• TRIM(...) — collapse whitespace and trim edges.
• LEN(...) = 0 — if after all that the cell has zero length, it’s effectively empty.

Apply across your required columns. Conditional-format EMPTY red. Now cells that LOOK filled but are actually invisible-whitespace surface immediately.

Bulk Cleanup — Replace the Invisibles

Once you’ve found them, empty them for real:

=IF(LEN(TRIM(CLEAN(SUBSTITUTE(B2, CHAR(160), "")))) = 0, "", B2)

Paste this in a helper column, drag down, copy, paste-values back over the original column. Now the cells that looked filled are genuinely blank, and your downstream checks work.

Catching More Characters

Non-breaking space is the most common culprit, but a few others sneak in:

• CHAR(8203) — zero-width space. Invisible.
• CHAR(8204), CHAR(8205) — zero-width joiner/non-joiner. Invisible.
• CHAR(65279) — byte-order mark. Shows up at the start of UTF-8 files imported without specifying encoding.

The safer formula strips all of them:

=IF(LEN(TRIM(CLEAN(
  SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,
    CHAR(160), ""),
    CHAR(8203), ""),
    CHAR(8204), ""),
    CHAR(65279), "")))) = 0, "EMPTY", "FILLED")

Verbose, but catches all the invisible-whitespace types in one pass.

Gotchas

• Numbers vs. text. If the column is supposed to hold numbers, a text-space cell returns a “FILLED” flag but wouldn’t survive arithmetic. Check type with ISNUMBER in addition to emptiness.
• ISBLANK semantic gotcha. A cell with a formula that returns "" is also not ISBLANK — it’s empty-string, which is text. This can trip required-field checks that rely on ISBLANK alone. The LEN-based check handles both cases identically.
• The source matters. If your CSV/paste source reliably produces NBSP instead of plain space, the long-term fix is at ingest — normalize characters once, store clean data. Detection is the short-term surface; prevention is the durable fix.
• Copy-pasting the detection formula. When you copy the formula itself, the CHAR(160) literal inside SUBSTITUTE can sometimes itself get autocorrected by the source editor. Paste into the cell directly rather than through a rich-text intermediate.

If You Need the Full Workflow

The Standardize Title Metadata Across Your Catalog tutorial covers the broader required-field validation pattern. Validate an Avails Sheet Against IFTA + EMA Rules catches invisible-blanks as rule #1 (blank required fields), using the same LEN approach.

Download

The Avails Data Quality Scorecard ships with the LEN-based blank check on every required column. Paste in your avails and fake-empty cells flag red.