UNICHAR

What it does

UNICHAR(number)

UNICHAR maps a number to the text consisting of the single Unicode character with that code point.

Precisely: given a number n, truncate it to an integer, interpret that integer as a Unicode code point, and return a one-character string containing it. "One character" here means one Unicode scalar value — which, in the UTF-16 code-unit model this Handbook uses for worksheet text (see the value universe), is two code units when the code point is outside the Basic Multilingual Plane. So UNICHAR(128512) is one character but occupies two code units, and functions that count code units will say so.

The interesting part of UNICHAR is not the mapping, which is trivial, but the domain. Three regions of the integer line behave differently and the distinction is the whole function:

1. Valid scalar values — 1 through 0xD7FF and 0xE000 through 0x10FFFF. These are the code points that can be encoded in UTF-16 and stored in a worksheet string.
2. Surrogate code points — 0xD800 through 0xDFFF. These are not characters. They exist only as the two halves of a UTF-16 surrogate pair, and asking for one on its own is a request for half a character. Whether Excel refuses this or produces an unpaired surrogate is exactly the kind of question this Handbook does not answer from memory; see below.
3. Out of range — 0, negatives, and anything above 0x10FFFF. Microsoft's page documents these as errors.

This is why UNICHAR is the right tool for constructing test data. It is the only ordinary way to write an arbitrary code point into a worksheet from a formula, which makes it the entry point for probing every other text function's Unicode behaviour.

Arguments

UNICHAR(number)

There is only one argument, and only one subtlety in it: it is a number, so text that reads as a number converts (see coercion and lifting) and TRUE converts to 1. Fractional values are truncated toward zero rather than rejected — the Handbook states that as the expected behaviour for this family and lists it below as unverified.

The argument is a code point, not a byte and not a UTF-16 code unit. That distinction is invisible below 0x10000 and decisive above it.

Try it

Quirks

Result and edge cases

The return kind is Text: a string of one Unicode scalar, which is one or two UTF-16 code units.

UNICHAR is a scalar kernel and lifts elementwise over array arguments; the rules, including element-local errors, are in coercion and lifting. This is what makes UNICHAR(SEQUENCE(n) + k) the standard idiom for generating a character sweep, and it is worth knowing that a single bad element in that sweep yields an error in that cell only, not a failed call.

Specific edges:

• UNICHAR(10) and UNICHAR(13) produce line-break characters, which is the usual reason to reach for this function outside Unicode work — though CHAR is the more common idiom there.
• UNICHAR(160) produces the non-breaking space, the character TRIM does not remove. This pairing is the standard repair for web-pasted text.
• Errors propagate from the argument ahead of any domain check.
• An empty referenced cell delivers Empty, which is distinct from an omitted argument; see the value universe.

Errors

Microsoft's page is the authority on the documented error conditions and is linked below; the Handbook could not retrieve it while this entry was written, so this section states expectations to be checked against it rather than quoting it.

The status of the surrogate range 0xD800–0xDFFF is not something this page asserts at all. It is listed as an open probe below.

How to implement

Notes for implementers

• Truncate before validating, and say which you do first. UNICHAR(65.9) and UNICHAR(-0.5) land differently depending on the order, and the second is the interesting one: truncation toward zero turns -0.5 into 0, which is documented as an error anyway, but truncation toward negative infinity would give -1. Order matters for the error reason, and may matter for the error at all.
• Decide the surrogate policy explicitly. Rust's char, .NET's Rune, and Python's chr disagree about lone surrogates: two of them refuse, one produces them. Whichever your host language does by default will silently become your UNICHAR behaviour if you do not intervene.
• Do not route through UTF-8 and back. A round trip through a UTF-8 string type will reject or mangle exactly the inputs whose behaviour is most in question. Work in UTF-16 code units, which is the model the worksheet uses.
• The result must be able to hold an unpaired surrogate if you decide to admit one, which means your text type cannot be a validated-UTF-8 or validated-UTF-16 string. The value-universe chapter records that ill-formed UTF-16 is a reachable state in Excel by another route (interop truncation splitting a pair), so this is not a hypothetical requirement.
• Lift over arrays. The sweep idiom depends on it.

Rust code

3 unichar_fn "UNICHAR" (1, 1) [V "number"] |s, a, sv| {
    let n = num(s, a, 0)?;
    out_text(k::unichar(n))
}

pub(crate) fn unichar(n: f64) -> Result<ExcelText, WorksheetErrorCode> {
    let n = n.trunc();
    if !n.is_finite() || !(1.0..=0x10FFFF as f64).contains(&n) {
        return Err(WorksheetErrorCode::Value);
    }
    let codepoint = n as u32;
    if (0xD800..=0xDFFF).contains(&codepoint) {
        return Err(WorksheetErrorCode::NA);
    }
    let scalar = char::from_u32(codepoint).ok_or(WorksheetErrorCode::Value)?;
    Ok(text_from_str(&scalar.to_string()))
}

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

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• CHAR — the older, narrower counterpart. CHAR takes a number in a single-byte range and is interpreted through the system character set; UNICHAR takes a Unicode code point directly. CHAR is not deprecated and is not superseded by UNICHAR; the two answer different questions, and they agree only over the ASCII range.
• CODE — CHAR's inverse, and the older counterpart to UNICODE.
• TEXT, VALUE — unrelated despite the naming; those convert between numbers and formatted representations, not between numbers and code points.
• Readers confuse UNICHAR(65) with "65". It is "A".

Named variants

No named variants are recorded yet.