What does TEXTBEFORE return when the delimiter is not in the text?

It returns #N/A. Supply the sixth argument, if_not_found, to get something else instead — an empty string is the usual choice, written as =TEXTBEFORE(A2, "-", 1, 0, 0, ""). Wrapping the whole call in IFNA works too, but the built-in argument is clearer and does not hide other errors.

Can TEXTBEFORE handle multi-character delimiters?

Yes. The delimiter argument can be any string, such as "--" or "|". TEXTBEFORE will look for the exact sequence of characters you specify.

What is the difference between instance_num and match_end?

instance_num chooses which occurrence of the delimiter to cut at, while match_end (0 or 1) decides whether the search starts from the beginning (0) or the end (1) of the text.

Why does TEXTBEFORE sometimes return a numeric value instead of text?

If the text argument is a number, Excel coerces it to text before processing, so the result is still a text string. However, if you concatenate a number with text, the number is first converted to its string representation.

TEXTBEFORE function

TEXTBEFORE returns the substring before a given delimiter, letting you pick which occurrence and supply your own value when the delimiter is absent.

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

Generate a TEXTBEFORE formula

Describe what you need. The generator will reach for TEXTBEFORE where TEXTBEFORE is the right tool, and tell you when it is not.

Arguments

How TEXTBEFORE reads its arguments

Argument	Required	Description
text	Required	String or reference containing the text to search; non-text values are coerced to text, errors propagate.
delimiter	Required	String that marks where to cut; if empty, TEXTBEFORE returns an empty string, and if not found it triggers if_not_found or an error.
instance_num	Optional	Optional positive integer indicating which occurrence of delimiter to use; defaults to 1, zero or non-numeric values cause #VALUE!.
match_mode	Optional	Optional 0 (exact case-sensitive) or 1 (case-insensitive); defaults to 0, any other value returns #VALUE!.
match_end	Optional	Optional 0 (search from start) or 1 (search from end); defaults to 0, other values raise #VALUE!.
if_not_found	Optional	Optional value to return when the delimiter is absent; if omitted, TEXTBEFORE returns #VALUE!.

Returns

Returns a text string; if the delimiter is not found it can return a custom value or an error.

Availability

Excel: 365 · Google Sheets: Not available

Worked examples

1. Extract SKU from a combined SKU-Warehouse string

SKU	Warehouse	On Hand	Reorder Point	Cost
A100	East	120	100	5.5
B200	West	45	50	12.75
C300	North	200	150	3.2

=TEXTBEFORE(A2 & "-" & B2, "-")

Result: A100

The formula concatenates the SKU in A2 with a hyphen and the warehouse in B2, producing "A100-East". TEXTBEFORE then returns everything before the first hyphen, which is the SKU "A100".

2. Get the warehouse name from a "Warehouse:On Hand" label

SKU	Warehouse	On Hand	Reorder Point	Cost
A100	East	120	100	5.5
B200	West	45	50	12.75
C300	North	200	150	3.2

=TEXTBEFORE(B3 & ":" & C3, ":")

Result: West

B3 holds "West" and C3 holds 45. The concatenation yields "West:45". TEXTBEFORE looks for the colon and returns the text before it, which is the warehouse name "West".

3. Capture SKU and warehouse up to the second hyphen

SKU	Warehouse	On Hand	Reorder Point	Cost
A100	East	120	100	5.5
B200	West	45	50	12.75
C300	North	200	150	3.2

=TEXTBEFORE(A4 & "-" & B4 & "-" & A4, "-", 2)

Result: C300-North

A4, B4 and A4 are concatenated into "C300-North-C300". By specifying instance_num = 2, TEXTBEFORE returns everything before the second hyphen, giving the combined SKU and warehouse "C300-North".

Common errors

Which TEXTBEFORE error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	The delimiter does not exist in the text and no if_not_found argument was supplied.	Add a fourth argument, e.g. "Not found", to return a friendly message instead of an error.
#VALUE!	instance_num is set to 0 or a non-numeric value, which TEXTBEFORE cannot interpret.	Use a positive integer such as 1, 2, … to indicate which delimiter occurrence to use.
#REF!	The text argument refers to a cell that has been deleted or contains a reference error.	Correct the cell reference so it points to a valid cell containing the source string.

Tips and when to use something else

Use match_mode = 1 to ignore case when the delimiter might appear in different capitalisations.
When you need the part after the delimiter, switch to TEXTAFTER – it works the same way but returns the trailing substring.
Combine TEXTBEFORE with FIND or SEARCH if you need the position of the delimiter for further calculations.
If you only need a fixed number of left-most characters, LEFT is often simpler and faster.

Related functions

TEXTAFTERTEXTAFTER returns the substring that appears after a specified delimiter within a text string, optionally selecting which occurrence to use.LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.FINDFIND returns the numeric position of a substring within a text string, counting from a specified start point and respecting case sensitivity.SUBSTITUTESUBSTITUTE returns a new text string where specified characters or substrings are replaced, optionally limiting the change to a particular occurrence.XLOOKUPXLOOKUP returns the value from a specified return array that corresponds to a given lookup value, with optional handling for missing matches and search direction.

Frequently asked questions

What does TEXTBEFORE return when the delimiter is not in the text?: It returns #N/A. Supply the sixth argument, if_not_found, to get something else instead — an empty string is the usual choice, written as =TEXTBEFORE(A2, "-", 1, 0, 0, ""). Wrapping the whole call in IFNA works too, but the built-in argument is clearer and does not hide other errors.
Can TEXTBEFORE handle multi-character delimiters?: Yes. The delimiter argument can be any string, such as "--" or "|". TEXTBEFORE will look for the exact sequence of characters you specify.
What is the difference between instance_num and match_end?: instance_num chooses which occurrence of the delimiter to cut at, while match_end (0 or 1) decides whether the search starts from the beginning (0) or the end (1) of the text.
Why does TEXTBEFORE sometimes return a numeric value instead of text?: If the text argument is a number, Excel coerces it to text before processing, so the result is still a text string. However, if you concatenate a number with text, the number is first converted to its string representation.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator