LEFT

What it does

LEFT(text, [num_chars])

LEFT returns the first characters of a text string — a prefix of the requested length. Microsoft's article states it as returning the first character or characters in a text string, based on the number of characters you specify.

Precisely: with t the text and n the requested count, the result is the first min(n, LEN(t)) characters of t. There is no error for asking for more than exists; the article says that if num_chars is greater than the length of the text, LEFT returns all of the text. That saturating behaviour is what makes LEFT usable directly on ragged data without a guard.

The unit of "character" is shared with the rest of the slicing family and is discussed on LEN, where Microsoft documents a Compatibility Version 2 change to how surrogate pairs are counted. The LEFT article carries the corresponding note from the other side: it states that LEFT now handles Unicode surrogates through the Compatibility Version feature (Version 2). Whatever unit LEN counts in, LEFT should slice in — but the Handbook has not checked that they move together, and it is the single most consequential unchecked question about this family.

Arguments

text — required, the text string containing the characters to extract.

num_chars — optional. Per the article: it specifies how many characters to extract, it must be greater than or equal to zero, if it exceeds the text length the entire text is returned, and it defaults to 1 if omitted.

That default is the argument fact readers most often get wrong, in both directions. LEFT(a) is not "the whole string" and it is not an error; it is the first character. And because the default is 1 rather than 0, LEFT and MID do not agree on what an unspecified count means — MID has no optional count at all.

num_chars = 0 is explicitly admissible (the documented constraint is "greater than or equal to zero") and yields empty text.

Try it

Quirks

Result and edge cases

The return kind is Text.

• Zero count yields empty text, not an error.
• Count beyond the length yields the whole string, per the article.
• Non-text arguments convert first. LEFT(1234.5, 2) slices the rendering 1234.5, so it is 12. This is the reason LEFT is a poor tool for extracting digits from numbers: the rendering, not the value, is what gets cut, and the rendering depends on how the number formats under General.
• A negative count violates the documented constraint. The article states the constraint but does not state the consequence of breaking it; see "Errors".
• Empty cells, omitted arguments and arrays follow the shared call model (chapter 03).

Errors

Microsoft's article states that num_chars must be greater than or equal to zero, and does not name the error produced when it is not. The Handbook records the constraint as documented and the error value as unverified.

Error inputs propagate, and a value kind with no text conversion surfaces #VALUE! under the shared coercion rules (chapter 02).

How to implement

Notes for implementers

1. Saturate, do not error, on an over-long count. This is documented behaviour and it is the behaviour that makes the function composable.
2. Decide what a fractional count means. The documentation states a constraint on sign, not on integrality. Truncation toward zero is the natural reading; it is a decision, not a given.
3. Slice on the same unit the length function counts in, and — given the Compatibility Version change — parameterise that unit rather than hard-coding it.
4. Never split a surrogate pair silently. Under a code-unit slicer, LEFT on astral text can produce a lone high surrogate, which the value-universe chapter records as a reachable state. Producing it may be correct emulation; producing it unknowingly is a defect.

Rust code

6 left_fn "LEFT" (1, 2) [V "text", V "num_chars"] |s, a, sv| { left(s, a) }

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

• RIGHT is the mirror; MID generalises both with an explicit start position. All three are one family and should share one unit decision.
• LEN supplies the bound: LEFT(a, LEN(a)) is a, and that identity is the cheapest test that the family's unit choices agree.
• LEFTB is the byte-counting variant, documented in the same Microsoft article and marked there as deprecated.
• TEXTBEFORE is the modern delimiter-driven alternative and is usually what a formula written as LEFT(a, FIND(x,a)-1) actually wants — including because TEXTBEFORE can be told what to do when the delimiter is absent, where the LEFT/FIND composition simply errors.
• TRIM is the usual companion when the slice is being taken from fixed-width imported data.

Named variants

No named variants are recorded yet.