Extract text before a delimiter
=TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])| Parameter | Description |
|---|---|
text |
The text string to extract from. |
delimiter |
The character(s) that delimit the text. |
instance_num |
[optional] The instance of the delimiter in text. Default is 1. |
match_mode |
[optional] Case-sensitivity. 0 = enabled, 1 = disabled. Default is 0. |
match_end |
[optional] Treat end of text as delimiter. 0 = disabled, 1 = enabled. Default is 0. |
if_not_found |
[optional] Value to return when no match is found. #N/A is default. |
To extract the text that occurs before a specific character or substring, provide the text and the character(s) to use for delimiter in double quotes
=TEXTBEFORE("Jones,Bob",",") // returns "Jones"Note that you can use more than one character for delimiter. For example to extract the first dimension in the text string "12 ft x 20 ft", use " x "f
=TEXTBEFORE("12 ft x 20 ft"," x ") // returns "12 ft"The formulas below extract text before the first and second occurrence of a hyphen character ("-"):
=TEXTBEFORE("ABX-112-Red-Y","-",1) // returns "ABX"
=TEXTBEFORE("ABX-112-Red-Y","-",2 // returns "ABX-112"The formulas below extract the text that occurs before the last hyphen and the second to the last hyphen:
=TEXTBEFORE("ABX-112-Red-Y","-",-1) // returns "ABX-112-Red"
=TEXTBEFORE("ABX-112-Red-Y","-",-2) // returns "ABX-112"Normally, TEXTBEFORE does not treat the end of a text string as a delimiter. For example, by default, the formula below will return #N/A because there
=TEXTBEFORE("ABX-123-Red-XYZ","-",4) // returns #N/AIf we enable match_end by providing 1, the formula behaves as if a delimiter exists after "XYZ":
=TEXTBEFORE("ABX-123-Red-XYZ","-",4,,1) // returns "ABX-123-Red-XYZ"