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

**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 Excel for Microsoft 365, Excel 365 for Mac, and Excel for the web.

Tips:

- In older Excel versions, you can construct your own formula to get text before a specific character
- If you are looking to extract text
**after**a given character or word, then use the TEXTAFTER function.

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

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

Tip. To extract text before the Nth space, define the occurrence number using the 3^{rd} (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)`

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)`

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

`=TEXTBEFORE(A2, ",", -2)`

## 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, " -")`

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

## 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, ",")`

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 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 5^{th} 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.

## 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 4^{th} one) to 1 or TRUE.

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

`=TEXTBEFORE(A2, "x")`

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

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

## 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)

## 19 comments

How to add { } in below formula. If I give enter { } bracket gone.

{=TEXTBEFORE(text,delimiter,[instance_num] }

Hi! { } automatically appear in the array formula. But the TEXTBEFORE function does not need to be entered as an array formula.

Hi

How would I extract text after a number? In one word without blank.

For example ACTHND00 and i want to take ACTHND only.

Or AN000001 and i want to take AN only.

Thank you in advance

Hi! Your question contradicts the example. To extract the text before the number, try this formula:

=LEFT(A1,MATCH(TRUE,ISNUMBER(--MID(A1,ROW(A1:A50),1)),0)-1)

The MID function extracts characters from text sequentially. The ISNUMBER function is used to identify numbers. The MATCH function finds the position of the first number in the text. The LEFT function extracts the desired number of characters from the beginning of the text string.

Hi

I have 3 difference types of character see below, one is to retrieve the name, one to retrieve the date in brackets & one in time

Time =RIGHT(AA25,LEN(AA25)-SEARCH("-",AA25))

Massuk - 2:04.20

Sweet Leilani (15 Oct, 2006) - 1:26.93

Cent Home (NZ) (24 Feb, 2001) - 1:47.19

Below is the result that I'm looking for

Massuk ?

Sweet Leilani ?

Cent Home (NZ) ?

15 Oct, 2006 ?

24 Feb, 2001 ?

How to use one formula for all three difference

Name =LEFT(AA25,SEARCH("(",AA25)-1)

Date =MID(AA25,FIND("(",AA25)+1,FIND(")",AA25)-FIND("(",AA25)-1)

Regards

Tony

Hi! If the results are written in different cells, you need to use a separate formula for each cell. I hope I have understood the problem correctly.

Hi Alexander

I use three difference formulas. But with getting the Bate there is three difference characters.

Minus sign - Single brackets ( ) & Double brackets ( ) ( )

Massuk - 2:04.20

Sweet Leilani (15 Oct, 2006) - 1:26.93

Cent Home (NZ) (24 Feb, 2001) - 1:47.19

How to place the three difference characters searches in the below formula to get the Date only? Knowing one doesn't have a date but gives values error

Date =MID(AA25,FIND("(",AA25)+1,FIND(")",AA25)-FIND("(",AA25)-1)

Regards

Tony

Hi! To remove the error message and replace it with a blank or other text, use the IFERROR function:

=IFERROR(MID(AA25,FIND("(",AA25)+1,FIND(")",AA25)-FIND("(",AA25)-1),"")

Hi Alexander

The error message is fine, I can deal with that.

I know how to get the first or second brackets.

But how to find date with one formula form the two below

Sweet Leilani (15 Oct, 2006) - 1:26.93

Cent Home (NZ) (24 Feb, 2001) - 1:47.19

or how to place '-', & '('', & '( (', in the below formula

Date =MID(AA25,FIND("(",AA25)+1,FIND(")",AA25)-FIND("(",AA25)-1)

Regards

Tony

Hi! These text strings are completely different. Therefore, you cannot use the same formula. For the second string, you can use the TEXTAFTER and TEXTBEFORE functions. For example:

=TEXTAFTER(TEXTBEFORE(A1,")",2),"(",2)

Hi All,

having a query

can anyone of them please fix this issue not able to split no spacing text sentences for example

"Whatisyourname"

secondly,

Area location repeated twice for example "BengaluruBengaluruKarnataka" how to split this same way all over Karnataka location given.

Hi! If you don't have delimiters, you can't tell the Excel formula how to split the text.

How do I use this function and return the value in numbers instead of Text?

You can convert the string into number using VALUE function. The syntax will go like =VALUE(TEXTBEFORE(cell,delimiter)) or =VALUE(TEXTAFTER(cell,delimiter))

Hi

I want to remove ( - ) like this.

Eg: UPI-abcdefg-gfdsac-2@okhdfcbank-hdfc-3456789-sddfggg

After : gfdsac-2@okhdfcbank

How can I do that..?

Hi! To extract part of the text from a text string, use the TEXTBEFORE and TEXTAFTER functions.

=TEXTAFTER(TEXTBEFORE(A1,"-",4),"-",2)

Hi,

when multiple delimiters are found, the function return the first searching in the string or the position of the first delimiter?

such as

Textbefore("abcd",{"c","b"}) will return "ab" or "a"?

Please can you post more new function related to Excel 365.

Hi Laxman,

Some new functions are already posted:

More new functions are coming soon, please stay tuned!