Excel Formulas to Extract and Trim Text Inside Brackets

Manually isolating text within brackets in Excel is a tedious bottleneck for busy data teams. While standard funding sources and financial databases provide vital raw metrics, they often output consolidated, messy metadata strings. Automating this extraction grants analysts the ability to cleanse large datasets instantly. However, this method stipulates that each cell contains a single, matching set of square brackets. For example, extracting the cohort "FY25" from "Grant [FY25]" requires targeted string manipulation. Below, we will review the step-by-step Excel formulas to execute this text extraction effortlessly.

Excel is an indispensable tool for data analysis, but raw data is rarely perfect. Quite often, you will find yourself dealing with text strings containing valuable information locked inside brackets, parentheses, or braces. For example, you might have product list entries like Product Name [SKU-1234], contact lists formatted as John Doe (Finance), or server logs containing Error Code {ERR_502}.

Whether you need to extract the text inside those brackets, strip away excess spaces, or remove the brackets entirely, Excel provides several powerful methods to get the job done. In this comprehensive guide, we will explore both traditional and modern Excel formulas to trim and extract text inside brackets, catering to all Excel versions.

Understanding the "Trim" Goal

In Excel terminology, "trimming" can mean two different things depending on your objective:

• Extracting text: Pulling out only the substring that resides between the opening and closing brackets.
• Cleaning text: Removing leading, trailing, or double spaces from the extracted bracketed content.

We will cover both scenarios below, utilizing legacy formulas for older Excel versions (Excel 2019 and earlier) and streamlined, modern formulas designed for Excel 365 and Excel 2021.

Method 1: The Classic Formula (MID + FIND)

If you are working on a legacy version of Excel or need to share your workbook with users on older versions, the combination of MID and FIND is the industry-standard solution. This formula calculates the starting position of the text inside the brackets and measures its length to pull it out precisely.

The Formula

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

How It Works Step-by-Step

To understand this nested formula, let's break down its components using the sample text "Shipping [Priority Air]" in cell A2:

1. Locating the start: FIND("[", A2) looks for the opening bracket. In our example, the bracket is the 10th character. We add + 1 because we want our extraction to start with the character after the bracket (the "P" in "Priority"). The start position is therefore 11.
2. Locating the end: FIND("]", A2) looks for the closing bracket. In our example, it is the 23rd character.
3. Calculating the length: To tell the MID function how many characters to extract, we subtract the starting bracket's position from the ending bracket's position and subtract 1:
   23 (position of "]" ) - 10 (position of "[") - 1 = 12 characters.
4. Extracting the text: The MID function executes: =MID(A2, 11, 12), resulting in the clean output: "Priority Air".

Method 2: The Modern Excel Way (TEXTBEFORE & TEXTAFTER)

If you are using Microsoft 365 or Excel 2021+, you can say goodbye to complex mathematical formulas. Microsoft introduced new text-manipulation functions that make extracting text between brackets intuitive and readable.

The Formula

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

How It Works

This approach works from the inside out:

• Step 1: TEXTAFTER(A2, "[") extracts everything that appears after the opening bracket. If A2 is "Shipping [Priority Air]", this inner formula yields "Priority Air]".
• Step 2: TEXTBEFORE(..., "]") takes that result and extracts everything before the closing bracket. This strips away the closing bracket, leaving us with exactly "Priority Air".

This method is not only easier to write but also vastly easier to troubleshoot and maintain.

Handling Messy Spaces: Combining with the TRIM Function

Often, extracted text contains unwanted spaces. For example, if your source cell contains "Status [ Pending Approval ]", the formulas above will extract " Pending Approval " with spaces on both ends.

To solve this, nest your extraction formula inside Excel's native TRIM function, which automatically deletes all leading and trailing spaces, and reduces multiple consecutive middle spaces to a single space.

The Trimmed Classic Formula:

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

The Trimmed Modern Formula:

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

Preventing Errors: Adding IFERROR

What happens if a cell doesn't contain brackets at all? For instance, if A2 simply says "Ground Shipping".

Without error handling, the FIND and TEXTBEFORE functions will fail and return a frustrating #VALUE! or #N/A error. To keep your spreadsheets clean and professional, wrap your formulas in the IFERROR function. This allows you to define a fallback value (such as a blank cell or a custom message) if no brackets are found.

Safe Classic Formula:

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

Safe Modern Formula:

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

Tip: Replace "" with a descriptive term like "No Brackets Found" if you want to actively flag missing data.

Adapting to Other Bracket Types

Not all data uses square brackets. Fortunately, these formulas are highly adaptable. You simply need to replace the opening and closing delimiters within the formulas to match your data structure.

Advanced Scenario: Dealing with Multiple Brackets

Sometimes, your data cells contain multiple bracketed items, such as: "User [JDoe] Role [Admin]".

If you want to extract the content of the second set of brackets (e.g., "Admin"), the modern TEXTAFTER function makes this incredibly simple via its optional [instance_num] argument.

Extracting the Second Bracketed Instance (M365):

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

By putting a 2 as the third argument in TEXTAFTER, Excel skips the first opening bracket and targets the text following the second opening bracket.

Splitting Multiple Brackets into Separate Columns:

If you want to extract every single piece of bracketed text into its own column dynamically, you can combine TEXTSPLIT and TRIM:

=TEXTSPLIT(A2, {"[","]"}, , TRUE)

This powerful dynamic array formula splits the text wherever it encounters an open or close bracket, and automatically filters out empty strings, placing each clean value into neighboring cells.

Summary Checklist

• For maximum compatibility across older versions of Excel, use the MID + FIND combination.
• For clean, readable, and future-proof formulas in modern Excel, stick to TEXTBEFORE + TEXTAFTER.
• Always nest your extraction formulas inside a TRIM function to protect against unwanted spacing issues.
• Prevent ugly error outputs by wrapping your final formula in an IFERROR statement.