TEXTBEFORE

What it does

TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

TEXTBEFORE slices a string at one chosen occurrence of a delimiter and returns everything to the left of it.

The machinery is identical to TEXTAFTER and only the cut point differs. Let t be the text and d the delimiter. Under the chosen match mode, enumerate the occurrences of d in t left to right as index pairs (start_1, end_1), …, (start_k, end_k) with end_i = start_i + len(d). instance_num selects one pair — positive counts from the left, negative from the right. TEXTBEFORE returns the substring of t from position 0 up to the selected start_i. TEXTAFTER returns the substring from end_i to the end.

That single-symbol difference explains the whole family:

1. The delimiter is in neither result. TEXTBEFORE stops at start_i; TEXTAFTER resumes at end_i. Concatenating the two for the same occurrence reconstructs t minus one copy of the delimiter, not t.
2. A delimiter at position 0 yields the empty string. start_1 = 0 and the substring is empty. That is a successful cut, not a failure.
3. Occurrence indexing, not character indexing. For positional work use LEFT/MID.

Arguments

TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])

Argument meanings below follow Microsoft's documentation for the shared TEXTAFTER/TEXTBEFORE argument set, as retrieved from the TEXTAFTER page.

The third position is the one most often misread: instance_num is a signed occurrence index, not a character count. TEXTBEFORE("a-b-c", "-", -1) is "a-b" — everything before the last hyphen — which is the idiom for "strip the final segment" and is not obtainable by any positive index without first counting the delimiters.

if_not_found catches only the no-match case. An argument-domain error still surfaces through it.

Try it

Quirks

Result and edge cases

The return kind is Text, possibly the empty string. The function is a scalar kernel; array arguments are handled by the engine's lifting, described in coercion and lifting, and the spill of a lifted result is the host-side adaptation step in the call pipeline.

Further boundaries:

• Non-text arguments convert by the ordinary rules; TRUE becomes the string TRUE, a number becomes its general-format rendering.
• An empty referenced cell delivers Empty, which the model keeps distinct from an omitted argument — see the value universe.
• Errors propagate rather than being absorbed by coercion.
• Empty string and #N/A mean different things. Empty means the cut succeeded with nothing to its left; #N/A (or if_not_found) means the occurrence did not exist. A caller that treats them alike will silently mishandle leading delimiters.

Errors

The error table below is taken from Microsoft's TEXTAFTER page, which the Handbook retrieved while writing this entry; the TEXTBEFORE page was not retrievable at that time. The two functions share an argument set, so the same conditions are the expected ones here — but that is an inference, and the TEXTBEFORE page is the authority for TEXTBEFORE.

if_not_found replaces the #N/A only. Errors arriving in arguments propagate ahead of both rows. Empty text gives #N/A, not #VALUE!, even though the default instance_num of 1 exceeds its length of 0 (FN-TEXTSPLIT-001).

How to implement

Notes for implementers

• Fix an overlap policy for multi-character delimiters and record it. Scanning "aaa" for "aa" gives a different occurrence count depending on whether you advance by one character or past the match. Microsoft's page does not state which Excel does.
• Resolve negative instance_num against the same occurrence list you built left to right. A separate right-to-left scan can disagree for overlapping delimiters.
• Case-insensitive matching is a folding choice. The upstream contract records ASCII folding only as observed. A full Unicode fold may find occurrences Excel does not.
• String indices are UTF-16 code units in this Handbook's model; astral characters make the choice observable. See the value universe.
• Do not lift the delimiter position on symmetry grounds. The evidence admits text and instance_num; inventing the third is how invented behaviour ships.

Rust code

13 textbefore_fn "TEXTBEFORE" (2, 6) [V "text", R "delimiter", V "instance_num", V "match_mode", V "match_end", V "if_not_found"] |s, a, sv| { text_delim(s, a, sv, Side::Before) }

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

• TEXTAFTER — the mirror function; identical arguments, opposite side of the cut, shared implementation module upstream.
• TEXTSPLIT — cuts at every occurrence at once and returns an array.
• LEFT, MID — positional slicing, for when you know the index rather than the marker.
• FIND / SEARCH with LEFT is the legacy idiom, still fully supported. LEFT(t, FIND(d,t)-1) is roughly TEXTBEFORE(t, d) except that it errors instead of returning #N/A, has no negative indexing, and needs SEARCH swapped in for case-insensitive matching.
• TEXTBEFORE supersedes nothing; the older composition remains valid.

Named variants

No named variants are recorded yet.