This function returns text that occurs after a given character or string.
=TEXTBEFORE(text,delimiter,[instance_num], [match_mode], [match_end], [if_not_found])
The function has the following arguments:
|
Argument |
Description |
|---|---|
|
text |
Refers to the text you are searching within. Wildcard characters are not allowed. If the text is an empty string, it returns an empty text. |
|
delimiter |
Refers to the text that marks the point before which you want to extract. |
|
instance_num |
[Optional] Refers to the instance of the delimiter after which you want to extract the text. By default, instance_num = 1. A negative number starts searching text from the end. |
|
match_mode |
[Optional] Determines whether the text search is case-sensitive. The default is case-sensitive. You can enter one of the following:
|
|
match_end |
[Optional] Treats the end of the text as a delimiter. By default, the text is an exact match. You can enter one of the following:
|
|
if_not_found |
[Optional] Refers to the value returned if no match is found. By default, #N/A is returned. |
The TEXTBEFORE function matches immediately when searching using an empty delimiter value. It returns the entire text when searching from the front (if instance_num is positive) and, empty text when searching from the end (if instance_num is negative).
Returns string data.
=TEXTBEFORE("Little red Riding Hood's red hood","red") // result is "Little"
=TEXTBEFORE("Little red Riding Hood's red hood","red", 2) // result is "Little red Riding Hood's"
=TEXTBEFORE("Little red Riding Hood's red hood","Red") // result is #N/A
=TEXTBEFORE("Little red Riding Hood's red hood","Red", ,1) // result is "Little"
This function is available in product version 16.0 or later.