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

##### Table of Contents

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:

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:

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.

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

3. And tada! We have successfully used the `TEXTBEFORE`

function in Excel.

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.

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.

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

function.

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.