iOfficeAI/OfficeCLI · error · ArgumentException

Defined name ref '{refText}' contains '#' outside a known er

Error message

Defined name ref '{refText}' contains '#' outside a known error literal — not valid formula text.

What it means

A defined-name body contains a '#' that is not part of a recognized Excel error literal (#REF!, #N/A, #NAME?, #DIV/0!, #VALUE!, #NULL!, #NUM!, #SPILL!, #CALC!, #GETTING_DATA). Such text is not valid formula syntax. String literals (bodies containing a double-quote) bypass this check entirely, and known error literals are stripped from the probe first.

Source

Thrown at src/officecli/Handlers/Excel/ExcelHandler.Helpers.Validation.cs:641

    {
        // Defined-name bodies are full formulas — validating them properly
        // is out of scope (functions, unions, cross-part brackets, escaped
        // apostrophes are all legal). Reject only the empirically fatal
        // patterns that pass schema validation but make real Excel refuse
        // the file: doubled/trailing '!' ("乱码!!!") and stray '#' outside
        // the known error literals ("乱码###").
        // Formula-length ceiling (8192) applies to defined-name bodies too.
        ValidateFormulaLength(refText, "defined-name ref");
        var body = (refText ?? "").TrimStart('=').Trim();
        if (body.Length == 0) return;
        if (body.Contains('"')) return; // string literals — leave to Excel
        // Strip the known error literals first: "#REF!" legitimately ends
        // with '!' and must not trip the dangling-bang check below.
        var probe = System.Text.RegularExpressions.Regex.Replace(body,
            @"#(REF!|N/A|NAME\?|DIV/0!|VALUE!|NULL!|NUM!|SPILL!|CALC!|GETTING_DATA)",
            "", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
        if (probe.Contains('#'))
            throw new ArgumentException(
                $"Defined name ref '{refText}' contains '#' outside a known error literal — not valid formula text.");
        if (probe.Contains("!!") || probe.EndsWith("!", StringComparison.Ordinal))
            throw new ArgumentException(
                $"Defined name ref '{refText}' has a dangling '!' — a sheet qualifier must be followed by a range (e.g. Sheet1!$A$1:$B$5).");
    }

    /// <summary>Text to store in a numeric cell's &lt;v&gt;: the literal digits
    /// when already canonical (preserves >15-significant-digit values that
    /// double cannot represent), else the parsed double re-serialized.</summary>
    internal static string NormalizeNumericCellText(string text, double parsed)
        => CanonicalNumericLiteral.IsMatch(text)
            ? text
            : parsed.ToString(System.Globalization.CultureInfo.InvariantCulture);
}

View on GitHub (pinned to 1ced45e900)

Solutions

Remove stray '#' characters from the body.
Use a complete, recognized error literal (e.g. '#REF!') if an error value is intended.
If the body legitimately contains text with '#', wrap it in a double-quoted string literal to bypass the check.

Example fix

// before
wb.AddDefinedName("MyName", "Sheet1!A1#B2");

// after
wb.AddDefinedName("MyName", "Sheet1!A1");
// or, if a broken ref is intentional:
wb.AddDefinedName("MyName", "#REF!");

Defensive patterns

Strategy: validation

Validate before calling

static readonly Regex ErrorLiteral =
    new(@"#(REF!|N/A|NAME\?|DIV/0!|VALUE!|NULL!|NUM!|SPILL!|CALC!|GETTING_DATA)",
        RegexOptions.IgnoreCase);
static bool DefinedNameHasStrayHash(string body) {
    if (body.Contains('"')) return false;
    var probe = ErrorLiteral.Replace(body, "");
    return probe.Contains('#');
}

Try / catch

try { wb.AddDefinedName(name, refersTo); }
catch (ArgumentException ex) when (ex.Message.Contains("contains '#'")) {
    wb.AddDefinedName(name, refersTo.Replace("#", string.Empty));
}

Prevention

Build defined-name refs from validated sheet + range parts, not pasted text.
If a literal text value is intended, wrap it in double quotes to bypass the check.

When it happens

Trigger: Setting a defined-name refersTo whose body, after stripping known error literals and ignoring quoted strings, still contains a '#'. Examples: 'Sheet1!A1#B2', 'Total#2026', a half-typed '#REF'.

Common situations: Copy-paste from web text with broken refs; manually typed error literals that miss the trailing '!'; stray hash from a hashtag or anchor; truncated #REF! during edit.

Related errors

AI-assisted analysis of iOfficeAI/OfficeCLI@1ced45e900 (2026-08-13). Data as JSON: /api/errors/850c5768249e9b50. Report an issue: GitHub.