Excel TEXTBEFORE Function

The TEXTBEFORE function is a Text function that returns part before a given character or string. In this guide, we’re going to show you how to use the TEXTBEFORE function and go over some tips and error-handling methods.

Supported versions

Excel 365 (Office Insider Beta only)

TEXTBEFORE Function Syntax

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

Arguments

text	The text you want to split.
delimiter	The text to delimit the part you want to extract.
instance_num	Optional. The instance of the delimiter. The default is 1, which means to extract by the first delimiter. Enter a negative value to start search from end.
match_mode	Optional. Predefined integer to determine case-sensitivity at delimiter matching. 0: Case-sensitive (default) 1: Not case-sensitive
match_end	Optional. Treats the end of text as a delimiter. 0: Disabled (default) 1: Enabled
if_not_found	Optional. Value returned if there is not a match. By default, #N/A is returned.

TEXTBEFORE Function Example

TEXTBEFORE Function's Default Usage

The TEXTBEFORE function requires the string (text) and the delimiter string to return a string before a given delimiter with default options.

=TEXTBEFORE("Spider-Man","Man") // returns "Spider-"

Multiple delimiters

If your source data contains multiple delimiters, you can enter multiple delimiters as an array value.

=TEXTBEFORE("Spider-Man",{"-"," ","."}) //returns "Spider"
=TEXTBEFORE("Wonder Woman",{"-"," ","."}) // returns "Wonder"

Text before n-th delimiter

If you want to skip the first delimiter(s) before parsing the text, give the instance number of the target delimiter as instance_num argument.

=TEXTBEFORE("The One-Above-All","-",2) // returns text before 2nd delimiter: "The One-Above"

Text before -n-th delimiter

You can give instance_num argument to a negative number to return text before the last occurrence of delimiter.

=TEXTBEFORE("The One-Above-All","-",-1) // returns "The One-Above"
=TEXTBEFORE("The One-Above-All","-",-2) // returns "The One"
=TEXTBEFORE("The One-Above-All","-",-3) // returns #N/A

Case-sensitive delimiter matching

By default, the TEXTBEFORE function applies a case-sensitive search to find the delimiters. You can make the delimiter search not case-sensitive by giving "1" to the match_mode parameter.

=TEXTBEFORE("Spider-Man","man",,1) // returns "Spider-"
=TEXTBEFORE("Wonder Woman","man",,1) // returns "Wonder Wo"

Match end of the text

You can determine the end of the text is treated as a delimiter or not. This feature is disabled by default. You can enable it to return entire text if delimiter doesn't exist.

=TEXTBEFORE("Wonder Woman","-") // returns #N/A error by default
=TEXTBEFORE("Wonder Woman","-",,,1) // returns "Wonder Woman" when match_end equals 1

If a delimiter is not found

You can define a custom text or cell reference to return when the function cannot find the given delimiter(s).

=TEXTBEFORE("Wonder Woman","-",,,,"Delimiter is not in this castle!") // returns "Delimiter is not in this castle!"

Download Workbook

Remarks

The function doesn't return the delimiter string or character itself.
Use the TEXTAFTER function to retrieve a part after the delimiter.
If you need to split text into multiple parts, prefer TEXTSPLIT function.
If delimiter is not found, the function returns #N/A error.