Exceljet

Quick, clean, and to the point

Excel TEXTBEFORE Function

Excel TEXTBEFORE function
Summary 

The Excel TEXTBEFORE function returns the text that occurs before a given substring or delimiter. In cases where multiple delimiters appear in the text, TEXTBEFORE can return text before the nth occurrence of the delimiter.

Purpose 
Extract text before delimiter
Return value 
Extracted text
Syntax 
=TEXTBEFORE (text, delimiter, [instance_num], [ignore_case])
Arguments 
  • 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.
  • ignore_case - [optional] Case-sensitivity. TRUE = off, FALSE = on. Default is FALSE.
Version 
Usage notes 

The Excel TEXTBEFORE function extracts text that occurs before a given substring or delimiter. When multiple delimiters appear in the text, TEXTBEFORE can return text that occurs before the nth instance of the delimiter. Unlike the TEXTSPLIT function, the output from TEXTBEFORE is a single value.

TEXTBEFORE takes a total of four arguments, only two of which are required. The first argument, text, is the text string to extract from. The second argument, delimiter is the substring to use 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). By default, instance_num is 1. The last argument in TEXTBEFORE is ignore_case, which is a boolean value that controls TEXTBEFORE's behavior when looking for a delimiter. Ignore_case defaults to FALSE, which means TEXTBEFORE is case-sensitive by default.

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:

=TEXTBEFORE("Jones,Bob",",") // returns "Jones"

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:

=TEXTBEFORE("12 ft x 20 ft"," x ") // returns "12 ft"

Note we include the space before and after x, since all three characters function as a delimiter.

Nth occurrence of delimiter

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("ABX-112-Red-Y","-",1) // returns "ABX"
=TEXTBEFORE("ABX-112-Red-Y","-",2 // returns "ABX-112"

TEXTBEFORE will return #N/A if the specified instance is not found.

Text before last occurrence

TEXTBEFORE supports negative values for instance_num, which makes it possible to return text before the last occurrence of delimiter like this:

=TEXTBEFORE("ABX-112-Red-Y","-",-1) // returns "ABX-112-Red"
=TEXTBEFORE("ABX-112-Red-Y","-",-2) // returns "ABX-112"

If instance_num is out-of-range, TEXTBEFORE returns an #N/A error.

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:

=TEXTBEFORE(B4,{", ",","})

TEXTBEFORE with more than one delimiter

Case-sensitivity

By default, TEXTBEFORE is case-sensitive when searching for delimiter. This behavior is controlled by the ignore_case argumenta boolean value that enables and disables case-sensitivity. By default, ignore_case is FALSE. In the example below, the delimiter appears as both " x " and " X " (upper and lower case "x"). The formula in D4 sets ignore_case to TRUE, which disables case-sensitivity and allows TEXTBEFORE to locate both versions of the delimiter:

=TEXTBEFORE(B4," x ",,TRUE) // disable case-sensitivity

TEXTBEFORE case sensitive example

Note: you can use 1 and 0 in place of TRUE and FALSE for the ignore_case argument.

Notes

  • TEXTBEFORE will return a #VALUE! 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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.