Textbefore Function

Purpose

Return value

Syntax

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

• 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.

Using the TEXTBEFORE function

The TEXTBEFORE function extracts text that occurs before a given delimiter. When multiple delimiters appear in the text, TEXTBEFORE can return text before the nth occurrence of a delimiter. TEXTBEFORE can also extract text that occurs before a given delimiter when counting from the end of a text string (i.e., get the text before the second to the last delimiter).

• The output from TEXTBEFORE is a single text string that occurs before a matched delimiter.
• TEXTBEFORE takes six arguments, but only the first two are required: text provides the text to process, and delimiter is the substring used to split the text.
• The instance_num argument indicates which instance of the delimiter to use. For example, to extract the text before the second instance of a delimiter, use 2 for instance_num . If not supplied, instance_num defaults to 1.
• By default, TEXTBEFORE is case-sensitive ( match_mode = 0) and will match case when looking for a delimiter. S et match_mode to 1 to ignore case when matching delimiters.
• By default, TEXTBEFORE will not treat the end of a text string like a delimiter ( match_end = 0). To enable this behavior, set match_end to 1.
• By default, TEXTBEFORE will return #N/A when it cannot find the specified delimiter. To return something other than #N/A, provide a value for if_not_found . Note that when match_end is enabled, it will override the value provided for if_not_found.

Note that Excel has three related functions that split text:

• Use TEXTSPLIT to extract all text separated by a given delimiter.
• Use TEXTBEFORE to extract the text before a given delimiter.
• Use TEXTAFTER to extract the text after a given delimiter.

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"

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 “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.

Text before with a positive instance number

By default instance_num is positive, and TEXTBEFORE will count instances of the delimiter starting from the left, as illustrated in the image below. To get all text before the first space, provide 1 for instance_num . To extract all text before the second space, provide 2. To get all text before the last word in the sentence (“dog”) provide 8 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 with a negative instance number

One of TEXTBEFORE special powers is that it also supports negative instance numbers, which makes it possible to work backward from the last delimiter . When instance_num is negative, TEXTBEFORE will count delimiters from the right , as seen below. To extract the last word in the sentence (“dog”), you would use -1 for instance number. To extract all text before “lazy”, use -2 for instance number, and so on

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"

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:

=TEXTBEFORE("ABX-123-Red-XYZ","-",4) // returns #N/A

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

This causes TEXTBEFORE to retrieve the entire text string because the text already contains 3 instances of the delimiter. Take care in situations where a delimiter cannot be found. For example, if match_end is enabled and instance_num is 1, TEXTBEFORE will return the entire string if the delimiter is not found. The video below demonstrates how the match_end argument can be used:

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,{", ",","})

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:

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

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.

Purpose

Return value

Syntax

=TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])

• text - The text string to split.
• col_delimiter - The character(s) to delimit columns.
• row_delimiter - [optional] The character(s) to delimit rows.
• ignore_empty - [optional] Ignore empty values. TRUE = ignore, FALSE = preserve. Default is FALSE.
• match_mode - [optional] Case-sensitivity. 0 = enabled, 1 = disabled. Default is 0.
• pad_with - [optional] Value to pad missing values in 2d arrays.

Using the TEXTSPLIT function

The TEXTSPLIT function splits a text string with a given delimiter into multiple values. The output from TEXTSPLIT is an array that will spill into multiple cells in the workbook.

Split text into columns or rows

TEXTSPLIT can split a text string into columns or rows. To use TEXTSPLIT, you will need to provide the text to split and a delimiter. You can either provide a column delimiter ( col_delimiter ) to split text into columns, or a row delimiter ( row_delimiter ) to split text into rows. For example, the formula below splits the text “red-blue-green” into separate values in columns:

=TEXTSPLIT("red-blue-green","-") // returns {"red","blue","green"}

Note that the column delimiter is provided as a hyphen ("-). If we move the hyphen ("-") to the row delimiter position, the TEXTSPLIT function will return the same values split into rows:

=TEXTSPLIT("red-blue-green",,"-") // returns {"red";"blue";"green"}

Note that both formulas above return an array of three values and the only difference is the location of the delimiter. The values in this first array will spill into separate columns , and the values in the second formula will spill into rows . The example below shows how this looks on the worksheet:

The first formula in cell D3 separates the three values into separate columns:

=TEXTSPLIT(B3,",") // returns {"Red","Blue","Green"}

The formula in cell D5 uses the same delimiter to split the text into separate rows :

=TEXTSPLIT(B3,,",") // returns {"Red";"Blue";"Green"}

In the second formula, the row_delimiter is left empty, and the same delimiter (",") appears as col_delimiter .

To summarize: provide a column delimiter if you want the results to be in separate columns and a row delimiter if you want the results to appear in separate rows.

TEXTSPLIT extracts all text separated by delimiters. Use TEXTBEFORE to extract text before a given delimiter, and TEXTAFTER to extract text after a given delimiter.

Behavior if no delimiter is found

If the provided delimiter is not found, TEXTSPLIT will return the original text unchanged. For example, if we use TEXTSPLIT on the text string “apple orange” with a period configured as the delimiter, TEXTSPLIT will return the original text:

=TEXTSPLIT("apple orange",".") // returns "apple orange"

The period (".") is not found, yet TEXTSPLIT behaves as if the delimiter was found at the end of the text. Both the TEXTBEFORE and TEXTAFTER functions have a similar feature, but it must be enabled with the match_end argument. With TEXTSPLIT, this behavior is automatic and useful .

Ignoring empty values

By default, TEXTSPLIT will include empty values in the text, where empty values are defined as two or more consecutive delimiters without a value in between. In practice, this means you will see empty cells in the worksheet when there is no value between delimiters, as you can see in the first formula below:

The formula in cell D3 does not include a value for ignore_empty , so empty values will appear:

=TEXTSPLIT(B3,",") // empty values will appear

To ignore (i.e., remove) empty values, set ignore_empty to TRUE, as in the second formula in cell D5:

=TEXTSPLIT(B3,",",,TRUE) // ignore empty values

In this case, TEXTSPLIT behaves as if the missing value does not exist at all. Only “Red” and “Green” are returned.

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

Match mode

The fifth argument, match_mode , determines case sensitivity when looking for a delimiter. By default, TEXTSPLIT is case-sensitive and match_mode is zero (0). Supply 1 to disable case sensitivity . In the example below the delimiter is " x " and " X “. The formula in D5 sets match mode to 1 to make TEXTSPLIT ignore case. As a result, the formula works for both cases:

=TEXTSPLIT(B5," x ",,,1)

Rows and columns

TEXTSPLIT can split text into rows and columns at the same time, as seen below:

In this case, an equal sign ("=”) is provided as col_delimiter and a comma (",") is provided as row_delimiter:

=TEXTSPLIT(B3,"=",",")

The resulting array from TEXTSPLIT contains 3 rows and 2 columns.

Padding

The last argument in TEXTSPLIT is pad_with. This argument is optional and will default to #N/A. Padding is used when the output contains rows and columns and a value is missing that would affect the structure of the array. In the worksheet below, “Blue” does not contain a quantity (there is no “=” delimiter). As a result, TEXTSPLIT returns #N/A where the quantity would go, to maintain the integrity of the array.

The formula in cell E3 contains does not specify a pad_with argument so the default value is returned:

=TEXTSPLIT(B3,"=",",") // default padding is #N/A

In cell E7, “x” is supplied for pad_with so “x” appears in cell F8 instead of #N/A.

=TEXTSPLIT(B3,"=",",",,"x")

Multiple delimiters

Multiple delimiters can be supplied to TEXTSPLIT as an array constant like {“x”,“y”} where x and y represent delimiters:

In the worksheet above, the text in B3 is delimited by both hyphens “-” and commas (","). The formula in cell F3 is:

=TEXTSPLIT(B3,{"-",","})

Notice also that there is an extra space separating green and purple. The TRIM function can be used to clean up extra space characters that appear in the output from TEXTSPLIT. The formula in F5 is:

=TRIM(TEXTSPLIT(B3,{"-",","}))

Notice the extra space that appears before purple in cell I3 is gone in cell I5.

Array of arrays

When using TEXTSPLIT, you might run into a limitation of the Excel formula engine where the formula will not return “arrays of arrays”. When TEXTSPLIT is used on a single cell, it returns the text in a single array, and values spill onto the worksheet into multiple cells. However, when TEXTSPLIT is used on a range of cells, TEXTSPLIT returns an “array of arrays”. The result may be a truncated version of the data or in some cases an error. Example here .