Excel Formulas to Extract Text Between Two Specific Characters

Isolating text between specific characters in Excel often leads to manual data-entry frustration when dealing with inconsistent string lengths. While standard funding sources for corporate data modernization prioritize complex enterprise ETL systems, business analysts frequently require immediate, localized solutions. Fortunately, mastering a nested formula grants you complete autonomy over your data preparation without relying on IT overhead.

Stipulation: This approach assumes your target boundaries-such as extracting "12345" from "TX-[12345]-US"-remain structurally consistent throughout the dataset.

Below, we outline the exact MID and SEARCH formula configuration to automate your text extraction seamlessly.

Data cleaning and manipulation are among the most common tasks performed in Microsoft Excel. Quite often, you will find yourself working with messy datasets where valuable information is buried inside a string of text. A classic example is needing to extract a substring that is enclosed within two specific characters-such as parentheses, brackets, hyphens, or slashes.

For instance, you might need to extract product codes from a text string like Product [PRD-9921] - In Stock, grab an email domain between "@" and ".", or retrieve a state abbreviation between two hyphens (e.g., TX from Dallas-TX-75201).

While this task might seem daunting at first, Excel provides several powerful ways to accomplish it. In this comprehensive guide, we will explore both traditional formulas (compatible with older Excel versions) and modern dynamic array functions (available in Excel 365 and Excel 2021) to find and extract text between two specific characters.

Method 1: The Classic MID and FIND Formula (Universal Compatibility)

If you are using Excel 2019, 2016, or older versions, the standard approach relies on combining the MID and FIND (or SEARCH) functions. This nested formula is highly reliable and works across all versions of Excel, including Excel Online and legacy desktop applications.

The Formula Template

To extract text between a starting character (let's assume it is "[") and an ending character (let's assume it is "]") from a cell in A2, use the following formula:

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

How It Works: A Step-by-Step Breakdown

The formula works from the inside out by calculating exactly where the target substring begins and how long it is. It uses three core arguments within the MID function: MID(text, start_num, num_chars).

• Step 1: Finding the Start Position (FIND("[", A2) + 1)
  The first FIND function locates the numerical position of the opening bracket "[". Because we do not want to include the bracket itself in our final extracted text, we add 1 to this position. This determines our start_num.
• Step 2: Calculating the Length of the Text (FIND("]", A2) - FIND("[", A2) - 1)
  Excel's MID function requires us to specify how many characters to extract (num_chars). To calculate this dynamically, we find the position of the closing bracket "]" and subtract the position of the opening bracket "[". Finally, we subtract 1 to adjust for the opening bracket, leaving us with the exact length of the text inside.
• Step 3: Extracting the Substring (MID)
  The MID function takes the original string in A2, goes to the calculated starting position, and extracts the exact number of characters computed in Step 2.

An Illustrative Example

Suppose cell A2 contains the text: Container [ID-48301] Arrived.

• FIND("[", A2) returns 11 (the bracket is the 11th character).
• The start position is 11 + 1 = 12 (where "I" in "ID" begins).
• FIND("]", A2) returns 20 (the closing bracket is the 20th character).
• The character length calculation is 20 - 11 - 1 = 8 characters ("ID-48301" is 8 characters long).
• The formula evaluates to =MID(A2, 12, 8), which correctly yields "ID-48301".

Method 2: The Modern Excel 365 Way (TEXTBEFORE and TEXTAFTER)

If you are running the modern version of Excel (Microsoft 365 or Excel 2024), you no longer need to wrestle with complex nested math. Excel introduced dedicated text manipulation functions that make this process incredibly intuitive and easy to write.

The Formula Template

By combining TEXTAFTER and TEXTBEFORE, you can cleanly peel away unwanted parts of your string like layers of an onion:

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

How It Works

This formula processes the text in two logical steps, moving from inside the parentheses to the outside:

1. TEXTAFTER(A2, "[") looks at cell A2, finds the first occurrence of "[", and returns everything to the right of it. Using our previous example, this outputs: "ID-48301] Arrived".
2. TEXTBEFORE(..., "]") takes that temporary output and trims off everything starting from the first occurrence of the closing bracket "]". This outputs: "ID-48301".

This approach is highly readable, less prone to manual mathematical entry errors, and runs faster on large datasets.

Method 3: Extracting Text Enclosed by the Same Character (e.g., Hyphens)

A common variation of this problem is extracting text located between two identical characters. For example, extracting the state code "TX" from Product-TX-Houston. Here, both the starting delimiter and ending delimiter are hyphens (-).

Using Modern Excel (Simplest Method)

With modern Excel, you can use the instance argument within TEXTAFTER and TEXTBEFORE, or simply use TEXTSPLIT to break the string into an array and choose the specific column.

If you want to extract the second element (the state) using TEXTSPLIT:

=INDEX(TEXTSPLIT(A2, "-"), 2)

This splits the text by hyphens into an array ({"Product", "TX", "Houston"}) and uses the INDEX function to grab the second item.

Using Classic Excel Formulas

If you are restricted to older Excel versions, finding the second instance of a character requires telling the second FIND function where to start looking. You do this by passing the first find position as the third parameter (start_num):

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

Here, FIND("-", A2, FIND("-", A2) + 1) translates to: "Find the hyphen in cell A2, but start searching only after the first hyphen is found." This dynamically locates the second hyphen.

Handling Errors and Edge Cases

Real-world data is rarely perfect. There will be times when your target characters are missing from some cells. When this happens, classic formulas will return a frustrating #VALUE! error because the FIND function cannot locate the character.

Preventing Errors with IFERROR

To keep your worksheets looking clean and professional, wrap your formula in an IFERROR statement. This allows you to output a blank cell or a custom message instead of an Excel error:

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

Case Sensitivity (FIND vs. SEARCH)

While characters like brackets, slashes, and hyphens do not have lowercase or uppercase variants, letters do. If you are searching for text between specific letters-for example, between "Start" and "End":

• Use FIND if case sensitivity is crucial (it treats "Start" and "start" differently).
• Use SEARCH if you want to ignore capitalization (it treats "Start", "START", and "start" identically).

Summary Comparison Table

To help you choose the best extraction method for your spreadsheet, consult the comparison table below:

Conclusion

Extracting text between two specific characters in Excel is a fundamental skill that significantly speeds up your data preparation workflows. If you are working on a shared corporate document that might be opened by users on older legacy versions of Excel, stick to the robust, classic MID + FIND nested formula. However, if you and your organization are updated to Microsoft 365, leverage the elegant TEXTAFTER + TEXTBEFORE formula to save time, reduce syntax errors, and keep your logic simple and clean.