The TEXTBEFORE function returns the portion of a text value preceding the appearance of a sequence of characters designated as being a delimiter. By default, everything preceding the first appearance of the delimiter is returned; the search for the delimiter is case sensitive; and the end of the text value being searched is not treated as being a delimiter. These behaviours can be overridden through the instance_num, match_mode and match_end arguments.
Invocations of the TEXTBEFORE function take the form:
The formula instructs the TEXTBEFORE function to perform a case-sensitive search of the text value “AxBxCXDXE” for the delimiter “X”. Since (1) the value supplied to the instance_num argument is negative, the search will traverse text from right to left, and (2) the value supplied to the match_end argument is 1 the left end of text will be treated as being a delimiter. The first instance of “X” found by the search is between “D” and “E” and the second between “C” and “D”. The search ignores the “x” between “B” and “C”, and “A” and “B” since they are lower case. Finally, the search reaches the point preceding “A” which is treated as being the 3rd delimiter. TEXTBEFORE then returns everything preceding it, which is the empty text value. The complete process is illustrated below:
A text value that will be searched for the value supplied to the delimiter argument. The portion of text preceding the appearance of delimiter will be returned by TEXTBEFORE.
delimiter
Mandatory
A sequence of 1 or more characters whose appearance in text marks the boundary between what will and will not be returned by TEXTBEFORE.
instance_num
Optional. Defaults to 1 if omitted.
An integer used to determine which instance of delimiter will be searched for. If instance_num is positive then text will be searched from left to right. If it is negative then text will be searched from right to left. Consider a situation in which text had the value “A-B-C-D” and delimiter had the value “-“. If instance_num were 3 then a left-to-right search for “-” would be conducted and “A-B-C” returned. If it were -3 then a right-to-left search would be conducted and “A” would be returned.
match_mode
Optional. Defaults to 0 if omitted.
Determines if TEXTBEFORE’s search for delimiter in text is case sensitive.
Value
Description
0
A case-sensitive search for delimiter in text is conducted.
1
A case-insensitive search for delimiter in text is conducted.
match_end
Optional. Defaults to 0 if omitted.
Determines if an end of text will be treated as being a delimiter. If match_end were set to 1 and instance_num were positive then the the right end of text would be treated as a delimiter. Conversely, if instance_num were negative then the left end of text would be treated as a delimiter. Consider a situation in which match_mode were set to 1, text had the value “A-B-C-D” and delimiter had the value “-“. If instance_num were 4 then TEXTBEFORE would treat the right end of text as the fourth delimiter and return “A-B-C-D”. If it were -4 then the left end would be treated as the fourth delimiter and the empty text value “” would be returned.
Value
Description
0
The end of text will not be treated as a delimiter.
1
The end of text will be treated as a delimiter.
if_not_found
Optional. Defaults to #N/A if omitted.
The value returned if no instance of delimiter exists in text which meets the search criteria.
The value supplied to the argument instance_num was 0.
#N/A
Runtime
It wasn’t possible to find an instance of the delimiter using the search criteria.
Examples
Example
Formula
Result
Description
1
=TEXTBEFORE("A-B-C","-",-2)
A
Perform a right-to-left search for the second “–” in “A-B-C“.
The “–” between “B-C” is the first delimiter found.
The “–” between “A-B” is the second delimiter found.
TEXTBEFORE returns all of “A-B-C” to the left of the second delimiter.
2
=TEXTBEFORE("A-B-C","-",2)
A-B
Perform a left-to-right search for the second “–” in “A-B-C“.
The “–” between “A-B” is the first delimiter found.
The “–” between “B-C” is the second delimiter found.
TEXTBEFORE returns all of “A-B-C” to the left of the second delimiter.
3
=TEXTBEFORE("AxBXC","X",1,0)
AxB
Perform a case-sensitive & left-to-right search for the first “X” in “AxBXC“.
Because it is lower case, the “x” between “A” and “B” is ignored.
The “X” between “B” and “C” is the first delimiter.
TEXTBEFORE returns all of “AxBXC” before the first delimiter.
4
=TEXTBEFORE("AxBXC","X",1,1)
A
Perform a case-insensitive & left-to-right search for the first “X” in “AxBXC“.
The “x” between “A” and “B” is the first delimiter.
TEXTBEFORE returns all of “AxBXC” before the first delimiter.
5
=TEXTBEFORE("A-B-C","-",3,1,1)
A-B-C
Treat the end of “A-B-C” as a delimiter and search it from left-to-right for the third “–“.
The “–” between “A” and “B” is the first delimiter.
The “–” between “B” and “C” is the second delimiter.
Because match_end is 1, the end of “A-B-C” is the third delimiter.
TEXTBEFORE returns all of “A-B-C” to the left of the third delimiter.
6
=TEXTBEFORE("A-B-C","-",3,1,0,"?")
?
Perform a case-insensitive & left-to-right search for the third “–” in “A-B-C“. Its end is not treated as being a delimiter.
The “–” between “A” and “B” is the first delimiter.
The “–” between “B” and “C” is the second delimiter.
Because match_end is 0, no more delimiters are found.
Because the search for the third “–” failed, “?” is returned.
Examples: Errors
Example
Formula
Result
Description
1
=TEXTBEFORE("AxBXC","X",2,0)
#N/A
Perform a case-sensitive & left-to-right search for the second “X” in “AxBXC“. Because TEXTBEFORE fails to find it and no value was supplied to if_not_found, #N/A is returned.
2
=TEXTBEFORE("A-B-C","-",0)
#VALUE
Supply a value of “0” to the instance_num argument.
