TEXTBEFORE
Split a string and keep the part before a separator, without counting characters.
Syntax
TEXTBEFORE(text, delimiter, [instance_num])| Argument | Required | Description |
|---|---|---|
text | Required | The text to split. |
delimiter | Required | The separator to look for. |
instance_num | Optional | Which occurrence to split at. Defaults to the first; negative counts from the end. |
Examples
Every example below was executed against VisiGrid engine 0.31.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=TEXTBEFORE("a-b-c","-") | a | |
=TEXTBEFORE("a-b-c","-",2) | a-b | Split at the second hyphen instead. |
=TEXTBEFORE("a-b-c","-",-1) | a-b | Negative counts from the end, so -1 is the last hyphen. |
=TEXTBEFORE("abc","-") | #N/A | No delimiter is #N/A, not the whole string. |
The formula you no longer have to write
Before this existed, taking the text before a separator meant:
=LEFT(A1, FIND("-", A1) - 1)Which is three functions, an off-by-one, and a #VALUE! when the delimiter is
missing. Now:
=TEXTBEFORE(A1, "-")Same answer, and the intent is legible at a glance.
Missing delimiters are #N/A
Excel’s choice, and it is the right one — but it means the no-match case is
#N/A rather than the untouched string. If you want the whole value when there
is no separator:
=IFNA(TEXTBEFORE(A1, "-"), A1)That is the common shape for splitting a column where only some rows have the separator.
Counting from the end
instance_num of -1 splits at the last occurrence, which is what you want for
file extensions, domain suffixes, and any path-like value where the tail is fixed
and the head has an unknown number of separators.
Pair it with TEXTAFTER to get both halves.
Excel compatibility
Matches Excel, including instance_num and negative instance_num.