Skip to content

Empty cell vs "" should be equal (matches Excel/Sheets) #106

Description

@garritfra

Surfaced while verifying the fix for #68 against Excel/Google Sheets behavior.

Current behavior

When a cell is empty:

  • A1=0TRUE ✓ (matches Excel/Sheets)
  • A1=\"\"FALSE ✗ (Excel/Sheets return TRUE)

This happens because resolve_cell in crates/cell-sheet-core/src/formula/eval.rs coerces an empty cell-ref to Number(0.0) before it reaches the equality logic:

Expr::CellRef(cell_ref) => {
    let val = resolve_cell(sheet, (cell_ref.row, cell_ref.col));
    if val == CellValue::Empty {
        CellValue::Number(0.0)   // <-- empty becomes 0
    } else {
        val
    }
}

So by the time the typed-equality arm sees the operands, the empty cell is already a number, and Number(0.0) == Text(\"\") is mixed-type → FALSE.

Expected behavior

In Excel / Google Sheets, an empty cell is treated as equal to both 0 (numeric context) and \"\" (text context). That is, empty has a kind of polymorphic identity that the spreadsheet picks based on context.

Possible designs

  1. Stop coercing Empty at resolve_cell; let the equality arm handle it. Inside Op::Eq | Op::Neq, treat Empty as equal to Number(0.0), equal to Text(\"\"), and equal to Empty. Other arms (arithmetic, ordering) still coerce empty to 0 explicitly. More invasive but cleaner separation.
  2. Special-case Text(\"\") in the equality arm. Currently Number(0.0) = Text(\"\") is FALSE. Add a rule: Number(0.0) = Text(\"\") and vice-versa is TRUE only when one operand originated from an empty cell. Requires tracking origin — ugly.
  3. Promote Text(\"\") to Empty when comparing. Localised to the equality arm: if either side is Text(\"\") and the other is Empty or Number(0.0), return TRUE. Simplest.

Option 1 is the most principled but ripples into other places that depend on the current empty-as-zero behavior. Worth a small design discussion before picking.

Acceptance criteria

  • A1=\"\" returns TRUE when A1 is empty.
  • A1=0 still returns TRUE when A1 is empty (don't regress).
  • \"\"=0 — decide and document. (Excel: TRUE. Sheets: TRUE.)
  • Tests in cell-sheet-core cover empty/\"\", empty/0, \"\"/0, and ensure non-empty text cells still compare correctly (e.g. A1=\"\" is FALSE when A1 contains "x").
  • Help entry for = / <> updated to mention empty/\"\" equivalence.

Out of scope

Whether ISBLANK(A1) should distinguish empty from \"\" — separate question. (In Excel, ISBLANK(\"\") is FALSE but \"\"=A1 is TRUE for empty A1. Asymmetric.)

Activity

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    enhancementNew feature or requestformula-engineFormula tokenizer, parser, evaluator

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions