FORMULATEXT

What it does

FORMULATEXT(reference)

FORMULATEXT(reference) returns the stored formula text of the referenced cell, including the leading equals sign. It inspects the workbook’s formula rather than converting the cell’s calculated value back into an expression.

Try it

Input cells in this preview hold values rather than authored formulas, so FORMULATEXT cannot retrieve a stored formula here.

In Excel put =7*6 in B2, then enter =FORMULATEXT(B2) elsewhere. It returns the text "=7*6", while B2 displays 42. The preview input grid currently supplies cell values, so its B2=1 control returns a worksheet error instead of an authored formula.

Quirks

Microsoft documents #N/A when the target has no formula, the formula exceeds 8192 characters, worksheet protection hides it, or an external source workbook is closed. Invalid argument types give #VALUE!. A multi-cell reference selects its upper-left cell. A self-reference can return its own text without normal circular calculation.

How to implement

Resolve the reference to its first cell and ask the workbook host for its stored formula text. Do not evaluate that formula to obtain the text and do not reconstruct spelling from the result. The current host interface distinguishes “known cell without formula” (#N/A) from “formula information unavailable” (a typed refusal); it also rejects a closed external workbook as #N/A. The host must supply formula length/protection policy and formula-bar spelling. This is a host-backed function, not a pure numeric kernel.

Rust code

5 formulatext_fn "FORMULATEXT" (1, 1) [F "reference"] |s, a, sv| { formulatext(s, a, sv) }

fn formulatext(site: &Site<'_>, a: &[CalcValue], sv: &mut EvalServices<'_>) -> Step<CalcValue> {
    let at = first_cell(site, a, 0)?;
    // dc-ylr: Excel gives FORMULATEXT of a reference to a closed workbook #N/A.
    if sv.host().is_some_and(|host| host.is_external_sheet(at.sheet())) {
        return fail(WorksheetErrorCode::NA);
    }
    match sv.host().and_then(|host| host.formula_text(at)) {
        Some(Some(text)) => Ok(crate::catalogue::kit::text_value(&text)),
        Some(None) => fail(WorksheetErrorCode::NA),
        None => Err(site.not_modelled(Some(0))),
    }
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

ISFORMULA checks whether a cell contains a formula. TEXT formats its calculated value rather than returning its formula text.

Named variants

No named variants are recorded yet.