UNICODE

What it does

UNICODE(text)

UNICODE returns the Unicode code point of the first character of a string.

The whole content of the function is in what "first character" means, and this is a place where a careless implementation and a careful one give different answers for real inputs. Worksheet text is a sequence of UTF-16 code units (see the value universe). If the first code unit is a high surrogate and the second is a low surrogate, they jointly encode one character in the supplementary planes. The choices are therefore:

• return the first code unit — which for an emoji returns a surrogate value in 0xD800–0xDBFF, a number that is not a code point at all; or
• decode the first Unicode scalar — which for an emoji returns the real code point, above 0xFFFF.

So the definition is: decode the first Unicode scalar of the argument text and return its code point as a number. Everything after the first character is ignored.

Arguments

UNICODE(text)

One argument, and one trap: the argument is text, so a number arrives as its general-format rendering and UNICODE(0) asks for the code point of the character "0" — that is, 48, not 0. The generated battery on this page shows exactly that shape of result, and it is a coercion consequence described in coercion and lifting, not a function-specific rule. Likewise UNICODE(TRUE) asks about the string TRUE and therefore about the letter T.

Only the first character matters. UNICODE("Hello") and UNICODE("H") agree.

Try it

Quirks

Result and edge cases

The return kind is Number — an integer-valued double, the code point.

UNICODE is a scalar kernel and lifts elementwise over array arguments, per coercion and lifting; UNICODE({"A","B"}) is a two-element numeric array. That makes UNICODE the natural companion to MID for dumping a string's code points: UNICODE(MID(t, SEQUENCE(LEN(t)), 1)), with the caveat that slicing a supplementary-plane character with MID may hand UNICODE half a pair — which is precisely the input the upstream note observed erroring.

Other edges:

• Empty text is an error, not zero. There is no first character to report.
• An empty referenced cell delivers Empty, distinct from an omitted argument; see the value universe.
• Errors propagate from the argument.
• The result is not bounded by 0xFFFF. Any implementation or consumer that assumes a 16-bit result is wrong for emoji and for most of the CJK extensions.

Errors

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

How to implement

Notes for implementers

• Decode, do not index. Reading s[0] in a UTF-16 buffer gives a code unit. Decode the first scalar, and handle the unpaired-surrogate case deliberately rather than by whatever your string type does when it panics.
• Do not route through UTF-8. A conversion step will reject or replace exactly the inputs whose behaviour is in question.
• The result is a Number, so it is a double. Every code point is exactly representable, but do not let a 16-bit integer type into the signature.
• Empty text must be checked before decoding, otherwise the failure surfaces as a panic or an out-of-range read rather than as #VALUE!.
• Lift over arrays, which is what makes the code-point-dump idiom work.

Rust code

4 unicode_fn "UNICODE" (1, 1) [V "text"] |s, a, sv| {
    let text = text_arg(s, a, 0)?;
    out(s, k::unicode(&text))
}

pub(crate) fn unicode(text: &ExcelText) -> Result<f64, WorksheetErrorCode> {
    let mut decoded = std::char::decode_utf16(text.utf16_code_units().iter().copied());
    match decoded.next() {
        Some(Ok(scalar)) => Ok(scalar as u32 as f64),
        Some(Err(_)) | None => Err(WorksheetErrorCode::Value),
    }
}

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

• UNICHAR — the inverse direction and the sibling in the same implementation module upstream. UNICODE(UNICHAR(n)) = n is the round trip.
• CODE — the older counterpart. Upstream's text-core contract records CODE as reading only the first UTF-16 code unit and rejecting empty text; UNICODE decodes a full scalar. Over the ASCII range the two agree, which is why the difference goes unnoticed until it matters. CODE is not superseded; it remains available and is the right function when you genuinely want the legacy character-set number.
• CHAR — CODE's inverse, and the narrower counterpart to UNICHAR.
• LEN and LENB — the other place where the scalar-versus-code-unit question is observable. Upstream records these two as having diverged for supplementary-plane characters under a current Excel compatibility version, with LENB retaining code-unit counting. Anyone reasoning about UNICODE should read that fork note before assuming a consistent counting model across the text family.
• Confused with VALUE, which converts a numeral string to the number it denotes — a completely different operation. UNICODE("5") is 53; VALUE("5") is 5.

Named variants

No named variants are recorded yet.