MID

What it does

MID(text, start_num, num_chars)

MID(text, start_num, num_chars) returns the contiguous run of num_chars characters of text beginning at position start_num, where positions are counted from 1 at the first character.

Written against the character positions of text, with n = LEN(text):

MID(text, s, k)  =  characters at positions s, s+1, …, min(s + k - 1, n)

and the empty string when s > n. Note what the clamp does and does not do: the end of the window is clamped to the end of text, so asking for more characters than remain is legal and simply yields the tail. The start is not clamped, so s < 1 is not "start at the beginning" — it is an error (see Errors).

"Character" here means one UTF-16 code unit, the unit the Handbook's value universe uses for worksheet text — the same unit LEN counts. A character outside the Basic Multilingual Plane occupies two code units, so MID can in principle cut a surrogate pair in half. See The value universe, "Text, exactly".

Arguments

All three arguments are required — num_chars has no default, unlike the optional second argument of LEFT and RIGHT. This is the position where the family is least uniform, and it is a frequent source of confusion: RIGHT(a) is legal and means one character, MID(a, 2) is not legal at all.

start_num and num_chars are numeric arguments, so ordinary to-number coercion applies to whatever arrives in those slots (a logical, numeric text, a referenced number). The general rules are in Coercion and lifting; nothing about them is specific to MID. Fractional values are the interesting case, and the Handbook has not checked how they are handled here — see "What has not been checked".

Try it

Quirks

Result and edge cases

Returns Text.

The boundary behaviours Microsoft documents on the MID page:

• start_num greater than the length of text → the empty string "", not an error.
• start_num within text but start_num + num_chars running past the end → the characters from start_num to the end of text.
• num_chars of zero → the empty string.

Empty, missing and error arguments follow the shared call model rather than any MID-specific rule; see The call pipeline and Coercion and lifting. An error value arriving in any argument propagates.

Errors

As documented on Microsoft's MID page:

Beyond those two, #VALUE! is also the expected result of a non-numeric-text value in a numeric argument slot under the shared coercion rules, and any error value passed in propagates. The Handbook has not verified the error surface against Excel.

How to implement

Notes for implementers

The start_num < 1 rule is the trap. It is tempting to saturate the window at both ends symmetrically, which silently turns MID(t, 0, 3) into a two-character result instead of the documented #VALUE!. Clamp the end; validate the start.

Counting in UTF-16 code units rather than Unicode scalar values is not an implementation convenience, it is the specified unit: an implementation that counts scalar values will disagree with Excel on any string containing an astral character, and will disagree in a way that only shows up on emoji and rarer CJK.

Rust code

8 mid_fn "MID" (3, 3) [V "text", V "start_num", V "num_chars"] |s, a, sv| { mid(s, a, false) }

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.

Recorded variants

Alternatives

Relationships

• LEFT and RIGHT are the two anchored slices; MID is the free one. LEFT(t, k) is MID(t, 1, k), and RIGHT(t, k) is MID(t, LEN(t) - k + 1, k) for 0 <= k <= LEN(t) — the second identity is exactly why RIGHT is not simply MID with a negative start.
• MIDB is the byte-counting sibling. Microsoft documents both on the same page and the Handbook splits them into two entries; see MIDB.
• REPLACE is the positional counterpart in the other direction: MID reads out a window by position, REPLACE writes over a window by position, with the same (start_num, num_chars) addressing.
• TEXTBEFORE, TEXTAFTER and TEXTSPLIT are the modern delimiter-based way to do what MID(…, SEARCH(…), …) formulas were traditionally written to do. MID is not superseded — positional slicing is still its own job — but a formula that computes start_num from a SEARCH result is usually clearer written with the newer functions.

Named variants

No named variants are recorded yet.