Extract Text Between Delimiters in Excel Using TEXTBEFORE and TEXTAFTER

Extracting specific substrings nested within cluttered data cells often leads to frustratingly complex, fragile Excel formulas. Much like navigating standard funding sources to secure business capital, finding the right starting point in legacy data parsing historically required tedious, multi-step verification. Fortunately, combining TEXTBEFORE and TEXTAFTER grants users immediate, streamlined control over their string manipulation.

An important stipulation to keep in mind is that these modern text functions are exclusive to Microsoft 365. This elegant nesting technique serves as the ideal tool to isolate SKU codes from long, delimited serial numbers. Below, we will examine the exact formula syntax and walk through a practical deployment guide.

Data cleaning and preparation are some of the most common yet time-consuming tasks in Microsoft Excel. For years, users had to rely on complex, nested formulas combining legacy functions like MID, LEFT, RIGHT, FIND, and SEARCH just to extract a specific piece of text located between two characters or delimiters.

Fortunately, Microsoft introduced two game-changing text manipulation functions in Excel 365 and Excel for the Web: TEXTBEFORE and TEXTAFTER. By nesting these two functions, you can easily extract any substring between delimiters without getting a headache from character-counting formulas. This article will show you exactly how to master this modern, elegant approach.

The Syntax of the New Heavyweights

Before combining them, let's briefly look at the syntax of each function to understand how they work individually.

1. The TEXTBEFORE Function

This function returns text that occurs before a given character or delimiter.

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

2. The TEXTAFTER Function

This function returns text that occurs after a given character or delimiter.

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

While both functions have several optional parameters for advanced use cases (like case-sensitivity and error handling), the core functionality requires only two arguments: the text you want to search and the delimiter you want to find.

The Core Formula: Nesting TEXTBEFORE and TEXTAFTER

To extract text between two different delimiters, you can nest one function inside the other. The conceptual logic is simple:

1. Use TEXTAFTER to strip away the starting delimiter and everything to its left.
2. Pass that result to TEXTBEFORE to strip away the ending delimiter and everything to its right.

The Formula Template

=TEXTBEFORE(TEXTAFTER(text, start_delimiter), end_delimiter)

Alternatively, you can reverse the order. This extracts everything before the end delimiter first, and then gets the text after the start delimiter:

=TEXTAFTER(TEXTBEFORE(text, end_delimiter), start_delimiter)

Both methods yield the exact same result in standard scenarios. Let's look at a practical example to see it in action.

Step-by-Step Example: Extracting Text inside Brackets

Imagine you have a list of system logs in column A, and you want to extract the severity code contained within square brackets.

To extract "ERROR-404" from cell A2, we identify our delimiters:

• Start Delimiter: "[" (open bracket)
• End Delimiter: "]" (close bracket)

Let's write the formula step-by-step:

Step 1: Apply the Inner Function

First, we isolate the text after the open bracket using TEXTAFTER:

=TEXTAFTER(A2, "[")

Result: "ERROR-404]: Database timeout"

Step 2: Wrap it with the Outer Function

Now, we take that temporary result and feed it into TEXTBEFORE to get everything before the close bracket:

=TEXTBEFORE(TEXTAFTER(A2, "["), "]")

Final Result: "ERROR-404"

Handling Multiple Identical Delimiters

What if your data uses the same delimiter multiple times? For example, consider a URL slug or a file path where words are separated by hyphens:

"finance-report-2023-final.xlsx"

Suppose you want to extract the year "2023", which sits between the second and third hyphens. By default, TEXTBEFORE and TEXTAFTER look for the first occurrence of a delimiter. However, we can use the optional [instance_num] argument to pinpoint exactly which hyphen we want.

To extract "2023" from "finance-report-2023-final.xlsx" (let's assume it is in cell A5):

1. We want the text after the second hyphen (which gets us "2023-final.xlsx").
2. From that result, we want the text before the first remaining hyphen (which gets us "2023").

The formula looks like this:

=TEXTBEFORE(TEXTAFTER(A5, "-", 2), "-")

Here, the 2 inside the TEXTAFTER function tells Excel to skip the first hyphen and split the text at the second hyphen. The outer TEXTBEFORE function then extracts everything before the very next hyphen it encounters.

Error Prevention: Handling Missing Delimiters

In real-world datasets, your data is rarely perfectly formatted. If one of your target delimiters is missing from a cell, Excel will return a #N/A error.

For example, if you run =TEXTBEFORE(TEXTAFTER("User Email: john.doe", "["), "]"), it will fail because there are no brackets in the string.

You can safeguard your spreadsheet in two ways:

1. Using the [if_not_found] Argument

Both functions have a built-in parameter to handle missing delimiters without wrapping your whole formula in an IFERROR statement.

=TEXTBEFORE(TEXTAFTER(A2, "[", , , , "Not Found"), "]", , , , "Not Found")

2. Using the Classic IFERROR Wrapper

For a cleaner-looking formula, you can wrap the entire nested statement in an IFERROR function to specify a default fallback value (like an empty string):

=IFERROR(TEXTBEFORE(TEXTAFTER(A2, "["), "]"), "Missing Delimiter")

How the Modern Formula Compares to Legacy Methods

To truly appreciate how simple TEXTBEFORE and TEXTAFTER make this task, let's compare the modern nested formula with the traditional formula used in older versions of Excel (Excel 2019 and earlier).

To extract the text between square brackets using legacy functions, you would have to write this monstrosity:

=MID(A2, FIND("[", A2) + 1, FIND("]", A2) - FIND("[", A2) - 1)

Why the Modern Nested Formula Wins:

• Readability: The intent of TEXTBEFORE(TEXTAFTER(...)) is clear at a glance. The legacy MID formula requires mental math to understand why we are adding and subtracting 1.
• Maintenance: If your delimiter changes (e.g., from brackets to curly braces {}), you only have to update the delimiter twice in the modern formula. In the legacy formula, you have to update it four times.
• Error Handling: The new functions handle edge cases natively via built-in arguments, whereas the legacy method requires complex logic or nested ISNUMBER/IFERROR checkups.

Summary: Quick Cheat Sheet

Keep this quick guide handy when working with text extractions in Excel:

By upgrading your workflow to use TEXTBEFORE and TEXTAFTER, you will save time, write fewer lines of formula code, and make your worksheets significantly easier to maintain.