Jun
22

Excel TEXTBEFORE function - extract text before character (delimiter)

by | updated on

In this tutorial, we will explore the Excel TEXTBEFORE function and how it can help you quickly extract text before any character or substring that you specify.

In earlier Excel versions, extracting text before a space, comma or some other character was quite tricky. First, you had to determine the position of the delimiter in a string using the SEARCH or FIND function, and then get all the characters before it with the help of LEFT. In Excel 365 and Excel for the web, we now have a brand-new TEXTBEFORE function that does the magic!

Excel TEXTBEFORE function

The TEXTBEFORE function in Excel is specially designed to return the text that occurs before a given character or substring (delimiter). In case the delimiter appears in the cell multiple times, the function can return text before a specific occurrence. If the delimiter is not found, you can return your own text or the original string.

The syntax of the TEXTBEFORE function has 6 arguments, of which only the first two are required.

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

text (required) - the original text to extract from. Can be supplied as a string or cell reference.

delimiter (required) - a character or substring that delimits the text.

instance_num (optional) - the instance of the delimiter before which to extract text. Default is 1. A negative number starts searching from the end of the original text.

match_mode (optional) - determines the delimiter's case-sensitivity. Enabled by default.

  • 0 (default) - case-sensitive
  • 1 - case-insensitive

match_end (optional) - treats end of text as delimiter. Disabled by default.

  • 0 (default) - match the delimiter exactly as specified in the formula.
  • 1 - match the delimiter against the end of text. Practically, it means that if the delimiter is not found, the function will return the original text.

if_not_found (optional) - the value to return if the delimiter is not found. If not set, a #N/A error is returned.

TEXTBEFORE availability

Currently, the TEXTBEFORE function is only available in the Office Insider Beta channel of Excel 365.

Tip. In older Excel versions, you can construct your own formula to Get text before a specific character.

Excel formula to extract text before character

For starters, let's get to know how to build a TEXTBEFORE formula in its simplest form.

Supposing you have a list of full names in column A and want to extract the first name that appears before the comma.

That can be done with this basic formula:

=TEXTBEFORE(A2, ",")

Where A2 is the original text string and a comma (",") is the delimiter. TEXTBEFORE formula to extract text before character

Extract text before first space in Excel

To get text before a space in a string, just use the space character for the delimiter (" ").

=TEXTBEFORE(A2, " ")

Since the instance_num argument is set to 1 by default, the formula will return text that appears before the first space.
Extract text before first space in Excel.

Tip. To extract text before the Nth space, define the occurrence number using the 3rd (optional) instance_num argument.

Get text before Nth instance of delimiter

To extract text that appears before the nth occurrence of the delimiter, supply the number for the instance_num parameter.

For example, to get text before the second occurrence of a comma, the formula is:

=TEXTBEFORE(A2, ",", 2)
Get text before Nth instance of delimiter.

Tip. If the specified occurrence is not found, TEXTBEFORE returns a #N/A error. Instead, you can configure the if_not_found argument to return the text you specify or set match_end to 1 to output the original text.

Return text before last occurrence of delimiter

To return text before the last occurrence of the specified character, put a negative value in the instance_num argument.

For example, to return text before the last comma in A2, the formula is:

=TEXTBEFORE(A2, ",", -1)
Return text before the last occurrence of the delimiter.

To extract text before the last but one comma, set instance_num to -2:

=TEXTBEFORE(A2, ",", -2)
Extract text before the last but one comma.

Extract text before substring

The delimiter argument of TEXTBEFORE can be a substring, i.e. a sequence of characters, which adds even more value to the function.

Let's say you have a list of employee names and their positions in one column, separated by a hyphen. The problem is that some names are hyphenated. To avoid splitting such names, we use a substring consisting of a space and a hyphen (" -") for the delimiter:

=TEXTBEFORE(A2, " -")
Extracting text before a substring

Get text before multiple delimiters

To handle different variations of the delimiter, you can use an array constant like {"a","b","c"} in your TEXTBEFORE formula, where a, b, and c represent different delimiters.

For example, if the delimiter is either a comma or a hyphen, with or without the preceding space character, you can handle all four variations correctly by providing the array constant {","," ,","-"," -"} for delimiter:

=TEXTBEFORE(A2, {","," ,","-"," -"})
Get text before multiple delimiters

If delimiter is not found, return custom text

If the specified delimiter is not found, the TEXTBEFORE function returns a #N/A error by default. To return a custom value instead, configure the if_not_found argument (the last one).

For example, if not a single comma appears in the source text (A2), this basic formula throws a #N/A error:

=TEXTBEFORE(A2, ",")
If no delimiter is found, the formula returns an error.

To get rid of the errors, type any text you want in the final argument, e.g. "Not found":

=TEXTBEFORE(A2, ",", , , , "Not found")

To return nothing, i.e. a blank cell, use an empty string for if_not_found:

=TEXTBEFORE(A2, ",", , , , "")
If the delimiter is not found, return an empty string.

If delimiter is not found, return original text

In some situations, it makes sense to return the original text if the TEXTBEFORE function cannot match the delimiter in the string. For this, just set the match_end argument (the 5th one) to 1. This will tell the formula to treat the end of text as delimiter.

=TEXTBEFORE(A2, ",", , ,1)

As you can see in the screenshot below, the formula now returns an employee name even if the delimiter (which is a comma) is not present in a cell.
If the delimiter is not found, return the original text.

Make delimiter case-sensitive or case-insensitive

By default, the Excel TEXTBEFORE function is case-sensitive, meaning it treats lowercase and uppercase delimiters as different ones. To disable case-sensitivity, set the match_mode argument (the 4th one) to 1 or TRUE.

For example, the below formula only accepts the lowercase "x" as the delimiter:

=TEXTBEFORE(A2, "x")
Case-sensitive TEXTBEFORE formula

Whereas an analogous formula with match_mode set to 1, recorgnizes both "x" and "X" as the delimiter:

=TEXTBEFORE(A2, "x", ,1)
Case-insensitive TEXTBEFORE formula

TEXTBEFORE function not working

If the TEXTBEFORE function does not work as expected or is not available in your Excel, it's likely to be one of these reasons.

If TEXTBEFORE does not exist in your Excel, please check out the list of supported versions.

If TEXTBEFORE returns a #N/A error, then either:

  • The delimiter does not exist in the source text. To handle the error, configure the if_not_found argument as explained in this example.
  • The instance_num value is greater than the number of the delimiter's occurrences in text.

If TEXTBEFORE returns a #VALUE! error, then either:

  • The instance_num argument is set to 0.
  • The instance_num value is greater than the total length of text.

That's how to extract text before character in Excel 365 with the help of the TEXTBEFORE function. I thank you for reading and hope to see you on our blog next week!

Practice workbook for download

TEXTBEFORE function – formula examples (.xlsx file)

You may also be interested in:

Table of contents