TEXTBEFORE takes six arguments; only the first two are required. The first argument, text, is the text string to process. The second argument, delimiter is the substring to use as a delimiter when extracting text. Both text and delimiter are required. The third argument, instance_num, is an integer that represents the nth instance of the delimiter in text (i.e. to extract the text before the second instance, use 2 for instance_num). If not supplied instance_num defaults to 1. The fourth argument is match_mode, which controls case sensitivity when looking for a delimiter. By default, TEXTBEFORE is case-sensitive and match_mode is zero (0). Supply 1 to disable case sensitivity. The fifth argument, match_end, allows TEXTBEFORE to handle the end of a text string like a delimiter. By default, match_end is 0 and TEXTBEFORE will not treat the end of a text string as a delimiter. Set match_end to 1 to make TEXTBEFORE use the end of the text string like a delimiter. The final argument is if_not_found, a custom value to return when TEXTBEFORE does not match any text. By default, TEXTBEFORE will return #N/A. See below for examples. Use TEXTBEFORE to extract text before a delimiter, TEXTAFTER to extract text after a delimiter, and TEXTSPLIT to extract all text separated by delimiters.
Basic usage
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 (""). For example, to extract the last name from “Jones, Bob”, provide a comma in double quotes (",") as delimiter: 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 “for delimiter: Note we include the space before and after x, since all three characters function as a delimiter.
Text before delimiter n
To extract text before the nth occurrence of delimiter, provide a value for instance_num. The formulas below extract text before the first and second occurrence of a hyphen character (”-"): TEXTBEFORE will return #N/A if the specified instance is not found.
Text before delimiter -n
TEXTBEFORE supports negative values for instance_num, which makes it possible to return text before the last occurrence of delimiter like this: If instance_num is out-of-range, TEXTBEFORE returns an #N/A error.
Match end of text
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 is no fourth delimiter: If we enable match_end by providing 1, the formula behaves as if a delimiter exists after “XYZ”: Essentially, this allows TEXTBEFORE to retrieve any text after the last delimiter. Take care in situations where a delimiter cannot be found. If match_end is enabled and instance_num is 1, TEXTBEFORE will return the entire string if the delimiter is not found.
Multiple delimiters
To provide multiple delimiters at the same time to TEXTBEFORE, you can use an array constant like {“x”,“y”} where x and y represent different delimiters. One use of this feature is to handle inconsistent delimiters in the source text. For example, in the worksheet below, the comma appears with (", “) and without (”,") a space character. By providing the array constant {", “,”,"} for delimiter, both variations are handled correctly:
Case-sensitivity
By default, TEXTBEFORE is case-sensitive when searching for delimiter. This behavior is controlled by the match_mode argument, a boolean value that enables and disables case-sensitivity. By default, match_mode is FALSE. In the example below, the delimiter appears as both " x " and " X " (upper and lower case “x”). The formula in D4 sets match_mode to TRUE, which disables case-sensitivity and allows TEXTBEFORE to match both versions of the delimiter:
Note: you can use 1 and 0 in place of TRUE and FALSE for the match_mode argument.
Notes
TEXTBEFORE is case-sensitive by default. TEXTBEFORE will return an #N/A error if delimiter is not found. TEXTBEFORE will return a #VALUE! error if text is empty TEXTBEFORE will return #N/A if instance_num is out-of-range.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
