TEXBEFORE Function in Excel: Explained

In this article, you will learn about the TEXTBEFORE formula and its uses in Excel.

‍

What is the TEXTBEFORE formula in Excel?

The TEXTBEFORE function in Excel is designed to extract text before a specific character or substring, known as the delimiter. This function also allows you to choose a particular occurrence of the delimiter in cases with multiple instances. If the delimiter cannot be found, the function can return an error message such as #N/A, your specific text, or the original string.

‍

What is the syntax of the TEXTBEFORE function in Excel?

The syntax of the Excel TEXTBEFORE function is as follows:

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

1. “text” (Required) - The text from which you will pull out a text string. Wildcard characters are not allowed in this function. 
2. “delimiter ” (Required) - The character or text that is the point before you want to quote. 
3. “instance_num” (Optional) - The instance of the delimiter before which you want to extract the text.  By default, “instance_num” is 1.  With -1  in this argument, the formula starts searching from the end and tries to find the first delimiter from the end of the text string. 
4. “match_mode” (Optional) - Determines whether the text search is case-sensitive. 0 or 1 can be input. 0 makes the function case-sensitive, and 1 makes it case-insensitive. The default is case-sensitive. 
5. “match_end” (Optional) - Treats the end of the text as a delimiter. You can enter 0 or 1. With 0 argument, the formula doesn’t match the delimiter against the end of the text. With 1 parameter, the function does the opposite.  By default, the text is an exact match. 
6. “if_not_found” (Optional) - Value returned when no match is identified. By default, #N/A is returned.

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

‍

How to use the TEXTBEFORE formula in Excel?

Understand the TEXTBEFORE function by looking at an example. The below dataset is of company employees and their designation, Employee ID records as retrieved from the legacy system. Now the objective is to extract the employee names from the dataset.

‍

Mapping of the dataset with the syntax of the TEXTBEFORE formula:

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

“text” will be Cell A2, as we need to retrieve the records from the cell.

“Delimiter” will be the symbol “|” as the employee names locate before the symbol.

We will use the function in the simplest form using only two required arguments.

Hence our complete function would look like this:

=TEXTBEFORE(A2,"|")

Now we apply this function in the corresponding cell B2 and press enter to show the outcome.

‍

‍

Then, you can simply copy the function to the other cells where you want to insert the formulas and extract similar data from corresponding cells.

‍

The TEXTBEFORE function extracts all the text present before the delimiter set in the formula from the Employee Records. If a record does not contain the delimiter defined in the TEXTBEFORE formula, it will return a #N/A value.

‍

As you can observe in the screenshot above, all the employee names are successfully pulled out from the records.

Look at another similar case where you need to fetch the user name from the list of email IDs.

‍

This time, we enter the function in cell B2 as follows: 

 =TEXTBEFORE(A2,"@")

Then, copy the formula to the other cells, and you can extract user names before the @ delimiter.

‍

In the following example dataset, extract text before the “nth” occurrence of the delimiter. We assume we need to fetch the employee names and designations from the list.

‍

We will use the following formula to extract the employee names and designations.

=TEXTBEFORE(A2,"|",2)

Where 2 indicates the 2nd occurrence of delimiter in the reference text.

‍

In the example below,  consider extracting text before the last occurrence of the delimiter. 

We will use the function defined as follows:

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

We enter a negative value, specifically -1, for the “instance_num” argument.

‍

Lastly, in case there is no text before the defined delimiter, we can set up the function to return as blank rather than a #N/A error.

In that case, the function should look something like this:

=TEXTBEFORE(A2,"|",,,,"")

The commas are required as per the function's syntax, though you can leave the irrelevant optional arguments blank here. To return a blank cell when there is no match, we insert an empty string ("") for the “if_not_found” attribute. Instead, you can put any character or word between the quotation marks in the “if_not_found” parameter.

‍

When is the TEXTBEFORE function in Excel beneficial? 

Here are some use cases for the TEXTBEFORE function in Excel:

1. Extracting user names from email addresses: As mentioned earlier, you can use the TEXTBEFORE function to quote the user name from an email address.
2. Extracting file names: If you have a list of file names in Excel and want to pull out their names, you can use the TEXTBEFORE function to extract the text before the "." character.
3. Cleaning data: Sometimes, text data in Excel can contain unwanted characters or symbols. You can use the TEXTBEFORE function to remove these characters and extract the clean text. For example, if you have a list of product codes that include a suffix after the actual code (such as "NM2023A2-9090"), you can use the TEXTBEFORE function to extract only the code and remove the "9090" suffix.

‍

What do you need to know about the TEXTBEFORE formula in Excel?

Note that the TEXTBEFORE function is only available in Excel for Microsoft 365 and Excel for the web. It is not available in earlier versions of Excel. Also, if you use an empty delimiter value when searching with the TEXTBEFORE function, it will return a match immediately. With zero delimiter value, when the instant_num parameter is positive and searching from the front, it returns nothing. Conversely, when the instance number is negative and searching from the end, it will return the entire text.

‍

Why does the TEXTBEFORE formula return an error value in Excel?

Below is the list of possible errors which occur as a result of the TEXTBEFORE function.

1. The TEXTBEFORE formula returns a #VALUE! error if “instance_num” is 0 or if “instance_num” is greater than the length of a certain text.
2. The TEXTBEFORE function returns a #N/A error if “delimiter” is missing in a text string searched.
3. You get a #N/A error if “instance_num” is greater than the number of occurrences of delimiter in a text string.