Returns the text that occurs before the specified text/string.
Syntax
TEXTBEFORE(text; delimiter; [instance]; [mode]; [match_end]; [if_not_found])
text: The text given as input. Eg., A2
delimiter: The text before which the result string is to be extracted. Eg., "is"
instance: The instance of the delimiter text to consider. Defaults to 1 if omitted. Negative numbers can be used to start search from the end of the given text. Eg., 2
mode: Specifies whether the search must be case-sensitive. Defaults to 0 if omitted.
0 - Case sensitive
1 - Case insensitive
match_end: Considers the end of the given text as the delimiter. Defaults to 0 if omitted.
0 - Don't consider end of text as delimiter
1 - Consider the end of text as delimiter
if_not_found: Text to be displayed when no valid match is found. Defaults to #N/A error if omitted. Eg., "Not found"
Examples
Formula | Result |
=TEXTBEFORE(A2;"is";2;1;;"Not found") | This |
=TEXTBEFORE(A2;"is";;1;;"Not found") | Th |
=TEXTBEFORE(A3;"@") | car |
Get a hands-on experience of the function in the embedded range below.
Possible Errors
Errors | Meaning |
#N/A! | |
#NAME! | The function name is incorrect or invalid. The given defined name (if any) is invalid. There is a typo in the defined name used in the function. Double quotes are missing for text values in the function. Colon missing in a cell range reference.
|
#VALUE! | |
#REF! | |
Similar Functions