Excel TEXTBEFORE function - extract text before character (delimiter)

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


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:


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 (" ").


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)


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

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

  2. 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:


      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.

  3. 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)



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



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



            • 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:


  4. Hi All,

    having a query

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

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

  5. 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..?

  6. 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"?

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

Post a comment

Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)