How to Use TEXTBEFORE Function in Excel

This guide will discuss how to use the TEXTBEFORE function in Excel

The rules for using the TEXTBEFORE function in Excel are the following:

  • If the delimiter is not found in the selected text, the function will return an #N/A error. 
  • The TEXTBEFORE function does not support wildcard characters. 
  • The output of the TEXTBEFORE function is a single value.
  • By default, the TEXTBEFORE function is case-sensitive.
  •  If the text argument is empty, the TEXTBEFORE function will return a #VALUE! error. 
  • If instance_num is out of range, the TEXTBEFORE function will return an #N/A error. 

So the TEXTBEFORE function will return a text or string that occurs before a given delimiter. But, let’s not confuse similar functions, such as the TEXTAFTER function and the TEXTSPLIT function. Essentially, the keywords from the function names are what can help us differentiate them. 

So the TEXTAFTER function will extract text that comes after a given delimiter, while the TEXTSPLIT function is used to extract all text separated by a given delimiter. Finally, the TEXTBEFORE function extracts text before a given delimiter. 

Let’s take a sample scenario wherein we used the TEXTBEFORE function in Excel.

Suppose you are a teacher who is updating the class records. And you want to make some changes, specifically creating a separate list with just the surnames of the students. But, it will take too much time and effort to manually type all the students’ surnames. 

To make this task easier, you opted to use the TEXTBEFORE function which will simply extract all the surnames from the original list of students. Afterward, you are done creating the new class list containing only the surnames of the students. 

Wasn’t that pretty simple? Before moving on, let’s first learn how to write the TEXTBEFORE function in Excel.

The Anatomy of the TEXTBEFORE Function

The syntax or the way we write the TEXTBEFORE function is as follows:

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

Let’s take apart this formula and understand what each term means:

  • = the equal sign is how we begin any function in Excel.
  • TEXTBEFORE() is our TEXTBEFORE function. And this will return the text that is before the specified or given delimiter. 
  • text is a required argument. And this refers to the text we want to extract and search the delimiter from. 
  • delimiter is also a required argument. So this refers to the character or string we want to use as a delimiter. 
  • instance_num is an optional argument. So it is referring to the desired occurrence of the given delimiter. By default, this is 1. Also, we can input a negative number which will search from the end.
  • match_mode is another optional argument. And this searches the selected text for a delimiter match based on what delimiter we input. By default, this is case-sensitive. 
  • match_end refers to matching the delimiter against the end of the text. But, they are not matched by default. Also, this is an optional argument. 
  • if_not_found refers to what value or character is returned if no match is found. But, a #N/A is returned by default. 

Amazing! Because we have discussed the syntax of the TEXTBEFORE function, let’s dive into a real example of using the function in Excel.

A Real Example of Using the TEXTBEFORE Function in Excel

Let’s say you want to create a new class record containing only the surname of the students in a class instead of their full names. But, starting from scratch would be a waste of time. Instead, we can use the old class list containing their full names and simply extract the surnames. 

Firstly, our original class list would look like this:

Initial dataset

 

Then, we can utilize the TEXTBEFORE function to simply extract the surnames of the students for our new class record. To be able to do this, there should be a delimiter. In this case, the surname and first name are separated by a comma, a common delimiter. 

Additionally, we can utilize the different arguments of the TEXTBEFORE function. For example, we want to extract the numbers before the nth occurrence of a given delimiter. And we can do that by using the instance_num argument.

Next, we can also use multiple delimiters by using an array constant. For example, the data set contains the delimiter comma, which sometimes appears with or without a space character. To avoid errors, we can input both cases to ensure we can extract all the correct values. 

Finally, we have successfully used the TEXTBEFORE function in our work, which would look like this:

Final output

 

You can make your own copy of the spreadsheet above using the link attached below. 

Then, let’s move on and discuss the steps of how to use the TEXTBEFORE function in Excel.

How to Use the TEXTBEFORE Function in Excel

In this section, we will discuss the step-by-step process of how to use the TEXTBEFORE function in Excel. Furthermore, we will explain how to utilize the different optional arguments in the function. 

1. Firstly, we can immediately use the TEXTBEFORE function to extract the students’ surnames. To do this, we must type the equal sign and the function name to begin.

Then, select the cell containing the text and the given delimiter used in the text. So our entire formula would be “=TEXTBEFORE(A2, “,”)”. Lastly, press the Enter key to return the results.

TEXTBEFORE function in Excel

 

2. Secondly, we can simply drag down the formula to copy it to the rest of the rows and immediately extract the surnames.

Drag down to copy formula

 

3. And tada! We have successfully used the TEXTBEFORE function in Excel.

Final result

 

4. Additionally, let’s learn how to use the instance_num argument. In this case, we want to get the values before the first and second occurrences of the delimiter. To do this, we can simply type in the formula “=TEXTBEFORE(E2, “-“, 2)”. Then, press the Enter key to return the values.

TEXTBEFORE function in Excel

 

5. Next, let’s try using multiple delimiters. For example, the space character after the delimiter is not consistent. To ensure a match is found, we will input both cases wherein the delimiter has a space character and another without a space character.

So input the formula “=TEXTBEFORE(A2, {“,”,”, “})”. Finally, press the Enter key to get the results.

TEXTBEFORE function in Excel

 

6. And tada! We have learned how to utilize the different arguments of the TEXTBEFORE function.

TEXTBEFORE function in Excel

 

And that’s pretty much it! We have successfully discussed how to use the TEXTBEFORE function in Excel. Now you can use the function whenever you need to in your work. 

Are you interested in learning more about what Excel can do? You can now use the TEXTBEFORE function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive ✨ early access ✨ to new content.



		
0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like