Excel TEXTBEFORE: How to Extract Text Before a Delimiter

How-to basics · Updated November 18, 2026

To return the text before a delimiter in Excel for Microsoft 365 on Windows, use =TEXTBEFORE(A2,"-") when A2 contains a value such as INV-204; the result is INV. TEXTBEFORE is a built-in function in Microsoft 365 and Excel 2024, and it can also select text before a later or earlier occurrence of a delimiter.

What TEXTBEFORE does

TEXTBEFORE separates a piece of a text value by locating a delimiter: a character or string that marks a boundary. Common delimiters include a hyphen, comma, space, slash, colon, or a word such as “at.” The function returns everything on the specified side of the matching delimiter, without including the delimiter itself.

This is useful when a cell combines information that you want to analyze separately. For example, a product code might contain a category and an item number separated by a hyphen, or a file path might contain folders separated by backslashes. TEXTBEFORE can pull out the leading part without changing the source cell. Because it is a formula, the result updates when the source text changes.

TEXTBEFORE is not the same as Excel’s Text to Columns command. Text to Columns can split data into separate columns as a one-time operation, while TEXTBEFORE returns a formula result in a cell. Use the function when you want an automatically updating result or need to combine the extraction with other formulas.

Basic syntax and delimiter examples

TEXTBEFORE has this general structure: =TEXTBEFORE(text,delimiter,[instance_num],[match_mode],[match_end],[if_not_found]). The first two arguments are the essentials: text is the value or cell to search, and delimiter is what Excel looks for. The remaining arguments are optional controls. Square brackets in the syntax indicate optional arguments; do not type the brackets into a formula.

With product codes in A2:A20 and a hyphen between each category and item number, enter =TEXTBEFORE(A2,"-") in B2 and fill it down. If A2 is BOOK-381, B2 returns BOOK. In Microsoft 365, you can also enter a formula that refers to a range and allow the result to spill into adjacent cells, provided those cells are empty; for straightforward row-by-row work, filling the formula down is easy to inspect.

The delimiter can be more than one character. With web addresses in A2:A20 where the text begins with https://, and the goal is to return the protocol prefix, a delimiter such as :// can identify the boundary. Remember that TEXTBEFORE returns text before the delimiter, not the delimiter itself; if the desired result needs to include the delimiter, join it back on or choose a different extraction method.

  • For team@example.com, use @ to return team.
  • For 2026/11/02, use / to return 2026.
  • For West, Region 4, use , to return West.

Choosing which delimiter occurrence to use

By default, TEXTBEFORE uses the first occurrence of the delimiter, counting from the beginning of the text. The optional instance_num argument changes which occurrence is used. A positive number counts from the left; a negative number counts from the right. The output is still all the text before the selected delimiter, so choosing a later occurrence can leave earlier delimiters in the result.

With values in A2:A20 that contain multiple hyphens, such as North-Store-17, and a goal of returning everything before the second hyphen, use =TEXTBEFORE(A2,"-",2). For that example, the result is North-Store. The second hyphen is the boundary; the first hyphen remains part of the returned text.

With file names in A2:A20 such as report.final.xlsx, and a goal of returning the text before the last period, use =TEXTBEFORE(A2,".",-1). The result is report.final, which retains the earlier period. This can be handy when a file name itself contains periods. If the goal is instead to remove everything from the first period onward, leave instance_num out.

Before choosing an occurrence, inspect representative values. If some rows have one delimiter and others have several, a formula set to instance 2 may return an error for rows with only one. In that situation, decide whether missing occurrences should produce a message, a blank, or the original value, and use the if_not_found argument or an error-handling function accordingly.

Case matching and missing delimiters

TEXTBEFORE is case-sensitive by default. The optional match_mode argument can change this: 0 means case-sensitive and 1 means case-insensitive. For example, “ID” and “id” are different delimiters with the default setting. This distinction matters in inconsistent imported data, such as labels where capitalization varies from row to row.

With labels in A2:A20 that may contain either id: or ID:, and a goal of returning the text before that marker regardless of capitalization, use =TEXTBEFORE(A2,"id:",1,1). For the value Order ID: 482, this returns Order because the function matches id: without regard to case. The trailing space is part of the text before the marker; if it should be removed, wrap the extraction in TRIM.

When Excel cannot find the delimiter, TEXTBEFORE normally returns #N/A. The optional if_not_found argument lets you specify a different result. With order codes in A2:A20 that may or may not contain a hyphen, and a goal of displaying a clear note when there is no hyphen, use =TEXTBEFORE(A2,"-",1,0,0,"No hyphen"). For ABC-12, the result is ABC; for ABC12, it is No hyphen.

The match_end argument is a separate option for treating the end of the text as a delimiter when the specified delimiter is not present. Most everyday extractions do not need it. If you use it, check the output against examples with and without the delimiter, because the result may be the entire source text when Excel treats its end as the boundary. A clear if_not_found value is often easier for a worksheet user to understand.

Practical workflow for a column of data

For a reliable result, start with a small sample and confirm what should happen for normal, unusual, and incomplete entries. Then place the formula in a new column rather than overwriting the source. A simple workflow is:

  1. Identify the exact boundary character or text, including spaces and punctuation.
  2. Check whether the first occurrence is always the intended one; if not, choose an instance number.
  3. Decide how to handle rows where the delimiter is absent or appears fewer times than expected.
  4. Enter the formula beside the first data row, check its result, and fill it down.
  5. Review the bottom of the list and a few exceptional rows before relying on the extracted values.

Spaces are a common source of surprises. If the data is Last name, First name, extracting before the comma returns the last name but may also retain a space before the comma if the source has one. TRIM can remove unwanted leading and trailing spaces from the extracted result. However, TRIM is not a universal cleanup tool for every character: imported nonbreaking spaces and other unusual characters may require separate cleanup.

When the extracted text will be used for matching or reporting, preserve the original column and label the new one clearly, such as “Category” or “Text before dash.” This makes it easier to compare the result with the source and to revisit the delimiter assumption if the input format changes.

Common errors and how to fix them

A #NAME? error often means the installed Excel version does not recognize TEXTBEFORE, or the function name was mistyped. TEXTBEFORE is available in Microsoft 365 and Excel 2024; it is not included in every older perpetual Excel release. A #N/A result usually means Excel could not find the delimiter and no alternative if_not_found result was supplied. A #VALUE! error can occur when instance_num is zero or requests an occurrence that does not exist.

Check these details before changing the formula:

  • Confirm that the delimiter is exactly what appears in the cell. A hyphen -, en dash –, and em dash — are different characters.
  • Look for extra spaces, hidden characters, or inconsistent punctuation in the source data.
  • Check that a positive instance number is not larger than the number of delimiter occurrences in a row.
  • Use match_mode 1 only if case differences should be ignored.
  • Use an if_not_found result when a missing delimiter is an expected possibility, rather than an exceptional mistake.

Also note that the displayed appearance of a cell can differ from its underlying value. A date may be displayed in a chosen format, for example, but formulas work with the cell’s stored value and formatting behavior. If you need a particular text representation from a number or date, first decide how it should be formatted as text; simply looking at the screen may not reveal what TEXTBEFORE is processing.

TEXTBEFORE compared with older methods

In older Excel versions without TEXTBEFORE, LEFT combined with FIND is a common way to return text before the first delimiter. FIND locates the delimiter’s position, and LEFT returns the characters before it. Unlike TEXTBEFORE, FIND is case-sensitive. It also returns an error when the delimiter is missing unless the formula handles that case.

With codes in A2:A20 separated by a hyphen, and a goal of returning the part before the first hyphen in an older Excel version, use =LEFT(A2,FIND("-",A2)-1). For INV-204, FIND locates the hyphen in position 4, and LEFT returns the first 3 characters: INV. To return the original text when there is no hyphen, use =IFERROR(LEFT(A2,FIND("-",A2)-1),A2); with INV204, this returns INV204.

TEXTSPLIT is another Microsoft 365 or Excel 2024 option when you want to divide text into multiple pieces, rather than return only the part before one boundary. For a one-off operation, Text to Columns may be more convenient. Flash Fill can recognize a pattern from examples, but it produces values rather than a formula that recalculates from the source. Choose based on whether you need one extracted section, several split sections, a reusable formula, or a static result.

Availability and version differences

TEXTBEFORE is available in Microsoft 365 and Excel 2024. Excel for the web also supports modern Excel functions, though menu layout and some editing details can differ from the Windows desktop app. On Mac, the function is available in supported Microsoft 365 and Excel 2024 releases as well; the formula itself is the same. If a workbook is opened in an older Excel version that does not support the function, the formula may display as an unsupported-function error.

Formulas in this guide use commas between arguments, as in standard English-language Excel settings. Some regional configurations use semicolons as argument separators. If Excel rejects a formula even though its structure is correct, check the list separator configured for your locale. The delimiter inside quotation marks is still the character you want to search for; changing the argument separator does not change that search character.

Frequently Asked Questions

How do I get everything before the first space in Excel?
Use TEXTBEFORE with a space as the delimiter. With text in A2, =TEXTBEFORE(A2," ") returns the first word or the text before the first space. It returns an error if there is no space unless you provide an if_not_found result.

Can TEXTBEFORE return text before the last delimiter?
Yes. Set instance_num to -1 to count from the right. For text such as report.final.xlsx, choosing a period as the delimiter and -1 returns report.final, the text before the last period.

Why does TEXTBEFORE return #N/A?
Most often, the delimiter is not present exactly as specified. Check for a different dash character, extra spaces, or a case mismatch. You can also use the if_not_found argument to return a message or blank instead of #N/A.

Does TEXTBEFORE work in older versions of Excel?
It is available in Microsoft 365 and Excel 2024, but not in all older Excel releases. In an older version, LEFT with FIND can return text before the first delimiter; use IFERROR if the delimiter might be missing.

Can I include the delimiter in the result?
TEXTBEFORE excludes the delimiter. If the delimiter should appear at the end of the returned text, concatenate it to the result, or use another method such as LEFT with a position calculation. Be sure to account for missing delimiters so the formula does not return an error unexpectedly.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.