Excel TEXTBEFORE Function

Syntax of the TEXTBEFORE Function

The TEXTBEFORE function takes up to six arguments (inputs inside the parentheses separated by commas). The first two are required; the remaining four are optional:

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

• text: The text string (or cell reference containing a text string) that you want to search within.
• delimiter: The character or substring that marks the boundary. TEXTBEFORE returns everything to the left of this delimiter.
• [instance_num]: Optional. Which occurrence of the delimiter to use. Defaults to 1 (the first occurrence). Use a negative number to count from the end of the string — for example, -1 finds the last occurrence.
• [match_mode]: Optional. Controls case sensitivity. Use 0 for a case-sensitive match (the default) or 1 for a case-insensitive match.
• [match_end]: Optional. Determines how Excel handles strings that do not contain the delimiter. Use 0 (default) to return an error when the delimiter is not found, or 1 to return the entire text string instead (treating the end of the string as the delimiter).
• [if_not_found]: Optional. The value to return if the delimiter is not found and match_end is 0. By default, Excel returns a #N/A error. Supply any text or value here to display something more user-friendly.

Using TEXTBEFORE in Excel

The most common use case for TEXTBEFORE is extracting a portion of a text string that comes before a predictable separator. Consider the following spreadsheet where column A contains full email addresses and we want to extract just the username (everything before the "@" sign) into column B:

To extract the username in cell B2, you would write:

=TEXTBEFORE(A2, "@")

The formula above looks at the text in A2 and returns everything that comes before the first "@" character. For A2, this returns john.smith. Dragging this formula down column B processes all remaining rows automatically.

TEXTBEFORE: The Easiest Way to Extract the First Word from Text in Excel

The same pattern works for any separator. To extract the first name from a "First Last" formatted name in cell A2, use a space as the delimiter:

=TEXTBEFORE(A2, " ")

For the text "Sarah Connor", this returns Sarah — everything before the first space.

Using the instance_num Argument

By default, TEXTBEFORE finds the first occurrence of the delimiter. But what if your delimiter appears multiple times and you want the text before the second or third occurrence? That is where the optional instance_num argument comes in. Consider a spreadsheet with hyphenated product codes in column A:

To extract just the category name (everything before the first dash) in B2:

=TEXTBEFORE(A2, "-")

This returns WIDGET. To instead get everything before the second dash (i.e., category plus color) in C2, set instance_num to 2:

=TEXTBEFORE(A2, "-", 2)

With instance_num set to 2, TEXTBEFORE finds the second "-" and returns everything to its left — giving you WIDGET-RED.

You can also supply a negative instance_num to count from the right. To get everything before the last dash (useful when you have a variable number of segments), use -1:

=TEXTBEFORE(A2, "-", -1)

For "WIDGET-RED-LARGE", this also returns WIDGET-RED, since the last dash is the second one. The negative instance_num approach is especially powerful when the number of delimiters in a string varies across rows.

Handling the if_not_found Argument

When the delimiter you specify does not appear in the text string, TEXTBEFORE returns a #N/A error by default. This can be disruptive when applying the formula across a large dataset where some cells may not contain the delimiter. The optional if_not_found argument lets you specify a clean fallback value instead.

Consider a list of employee emails where most have a department prefix separated by a dot, but a few legacy entries do not follow that pattern:

Without the if_not_found argument, the formula in B4 would return a #N/A error because "legacyuser" has no dot. To handle this gracefully, supply a fallback as the sixth argument:

=TEXTBEFORE(A2, ".", 1, 0, 0, "Unknown")

For rows that contain a dot (like "sales.jsmith"), the formula returns sales. For row 4, where no dot exists, it returns the friendly text "Unknown" instead of an error.

Note that when using if_not_found, you must also supply values for instance_num, match_mode, and match_end — even if you just use the defaults (1, 0, 0) — because Excel requires all preceding optional arguments to be present when specifying a later one.

Combining TEXTBEFORE with TEXTAFTER

TEXTBEFORE is often used alongside its counterpart, TEXTAFTER, to split a string into two or more parts. For example, if column A contains full names in "First Last" format and you want to separate them into distinct columns, TEXTBEFORE gets the first name while TEXTAFTER gets the last name:

In cell B2, to extract the first name:

=TEXTBEFORE(A2, " ")

In cell C2, to extract the last name:

=TEXTAFTER(A2, " ")

Together, these two formulas cleanly split "Sarah Connor" into "Sarah" (B2) and "Connor" (C2) without any complex nested logic. You can drag both formulas down their respective columns to process every row at once.

For more complex splits — for instance, extracting the middle segment of a three-part string like "prefix-value-suffix" — you can nest TEXTBEFORE inside TEXTAFTER (or vice versa). To isolate just "value" from "prefix-value-suffix":

=TEXTBEFORE(TEXTAFTER(A2, "-"), "-")

The inner TEXTAFTER first strips the prefix to give "value-suffix", and then TEXTBEFORE pulls everything before the remaining dash to return value.

TEXTBEFORE vs. the LEFT and FIND Approach

Before TEXTBEFORE was introduced in Microsoft 365, Excel users had to extract text before a delimiter using a combination of the LEFT function and the FIND function. For example, to extract the text before the "@" symbol in an email address, the classic approach required:

=LEFT(A2, FIND("@", A2) - 1)

This formula uses FIND to locate the position of the "@" character, subtracts 1 to exclude the "@" itself, and then uses LEFT to extract that many characters from the start of the string. While functional, this approach has several drawbacks compared to TEXTBEFORE:

• It is harder to read and understand at a glance, especially for colleagues less familiar with Excel.
• It returns a #VALUE! error when the delimiter is not found — requiring an additional IFERROR wrapper to handle missing delimiters gracefully.
• Targeting a specific instance of a delimiter (e.g., the second comma) requires further nesting with additional FIND calls, which grows complex quickly.
• Searching from the right side of a string requires replacing FIND with a lengthy formula involving SUBSTITUTE and LEN.
• Case-insensitive matching requires FIND to be replaced by SEARCH, adding yet another function to remember.

TEXTBEFORE handles all of these scenarios directly through its built-in arguments, making formulas dramatically shorter, cleaner, and easier to audit or hand off to a teammate.

Quick Reference: TEXTBEFORE Examples

The table below summarizes the most common TEXTBEFORE use cases at a glance.

The last example demonstrates match_mode set to 1 (case-insensitive), which means searching for "X" also matches the lowercase "x" in the string.

Availability: Which Versions of Excel Support TEXTBEFORE?

TEXTBEFORE is available in Excel for Microsoft 365 (both Windows and Mac) and Excel for the web. It was introduced as part of the same update that added TEXTAFTER, TEXTSPLIT, VSTACK, HSTACK, and a suite of other dynamic array functions designed to modernize how Excel handles text and data reshaping.

TEXTBEFORE is not available in Excel 2021, Excel 2019, Excel 2016, or any earlier perpetual-license version. If you are working in one of those versions, you will need to use the LEFT + FIND workaround described above. If you share workbooks with colleagues who use older Excel versions, their copies will display a #NAME? error wherever a TEXTBEFORE formula appears, since the function name is unrecognized in those versions.