[]
        
(Showing Draft Content)

TEXTBEFORE

This function returns text that occurs before a given character or string.

Syntax

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

Arguments

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:

  • 0      Case sensitive.

  • 1      Case insensitive.

match_end

[Optional] Treats the end of the text as a delimiter. By default, the text is an exact match. You can enter the following:

  • 0      Don't match the delimiter against the end of the text.

  • 1      Match the delimiter against the end of the text.

if_not_found

[Optional] Refers to the value returned if no match is found. By default, #N/A is returned.

Data Types

Returns string data.

Remarks

The TEXTBEFORE function matches immediately when searching with an empty delimiter value. It returns empty text when searching from the front (if instance_num is positive) and the entire text when searching from the end (if instance_num is negative).

Examples

=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"