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.
|
| match_end |
Optional. Treats the end of text as a delimiter.
|
| 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!"
