To extract text between parentheses in Excel, use MID with FIND to locate the opening and closing characters. With a string such as Batch (AB-204) ready in A2, enter =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1); the result is AB-204. MID returns characters from a position you specify, while FIND supplies the positions of the delimiters.
What MID and FIND do
MID extracts a specified number of characters from a text value. Its arguments are the text to read, the starting position, and the number of characters to return. Positions start at 1, so the first character is position 1—not position 0. The function can read from a cell, a typed string, or the result of another function.
FIND locates one text string inside another and returns the position where it begins. It is case-sensitive: finding ABC is not the same as finding abc. It can also take an optional starting position, which is useful when a delimiter appears more than once. If the text being sought is absent, FIND returns the #VALUE! error.
The two functions complement each other. FIND identifies where a piece of text starts or ends; MID uses those positions to determine what to extract. This is useful when the text to retrieve has a variable length, because the formula can calculate that length instead of relying on a fixed character count.
How to build the nested formula
Start with the cell containing the full string. Use FIND to locate the opening delimiter, then add 1 to start immediately after it. Use another FIND to locate the closing delimiter. The number of characters to return is the closing position minus the opening position minus 1, which excludes both delimiters.
With the example string in A2, the opening parenthesis is at position 7 and the closing parenthesis is at position 14. The starting position is therefore 8, and the character count is 14 − 7 − 1, or 6. MID returns the six characters beginning at position 8: AB-204. This position-based approach works even if the code inside the parentheses changes length.
Read the nested formula from the inside out. The first FIND locates the opening parenthesis. The second FIND locates the closing parenthesis. The subtraction between those positions calculates how many characters lie between them, and MID returns that span. Keeping the opening and closing searches visible in the formula is more reliable than manually counting characters in every row.
Worked examples and useful variations
For a table of product records, put one complete record in each cell in column A and enter the formula in B2. Then fill it down to process other rows. Each row’s delimiters determine its own start and length, so records do not need identical code lengths. For example, the same pattern extracts X7 from Item (X7) checked and AB-204 from Batch (AB-204) ready.
If the data has a colon followed by a semicolon rather than parentheses, search for those characters instead. With A2 containing Order: ZX-482; Qty: 6, this formula returns ZX-482:
With the full order string in A2, enter =MID(A2,FIND(": ",A2)+2,FIND(";",A2)-FIND(": ",A2)-2). It returns ZX-482: the colon-space sequence ends at position 7, the semicolon is at position 14, and the six characters between them are the code.
The exact delimiter matters. Searching for ": " includes a space in the match, so adding 2 starts after both characters. If some records have no space after the colon, searching for just ":" and adjusting the offset may suit the data better. Inspect a few actual records before applying a formula to a long list.
To find a later occurrence of a delimiter, use FIND’s optional starting-position argument. For instance, with A2 containing ID=17; ID=42, a formula can locate the second instance of ID= by starting the second search after the first match:
With ID=17; ID=42 in A2, enter =FIND("ID=",A2,FIND("ID=",A2)+1). The result is 8, the position of the second I. The inner FIND locates the first match at position 1, and the outer FIND begins searching at position 2. This technique is useful when a record contains repeated labels or separators.
Handling missing delimiters and other errors
The most common problem is #VALUE!, which usually means one of the FIND searches could not find its delimiter. Check for inconsistent punctuation, extra spaces, or records that are incomplete. FIND is case-sensitive, so a search for a capital letter will not match a lowercase version. Also confirm that the formula refers to the intended cell and that the delimiter characters are ordinary text, not visually similar characters copied from another source.
If missing delimiters should produce a blank rather than an error, IFERROR can provide a fallback. With records in A2 and the desired behavior being an empty result when the delimiters are absent, use:
With the record in A2, enter =IFERROR(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1),""). It returns the text between parentheses when both are present and a blank cell display if the calculation returns an error. IFERROR is convenient, but it can also hide unrelated problems in the formula, so it is sensible to verify the input data before treating every blank as an expected result.
A different error can occur if the closing delimiter appears before the opening delimiter. The calculated character count then becomes negative, which MID cannot use. A nested pair of parentheses can also confuse this simple pattern: FIND returns the first opening and first closing parenthesis, not a full understanding of nested structure. For complicated or inconsistent records, clean the source data or use a more deliberately designed parsing method rather than assuming the first pair always identifies the desired text.
Leading or trailing spaces inside the delimiters are returned as part of the extracted text. For example, if a value is written as ( AB-204 ), the result includes those spaces. You can remove surrounding spaces from the result with TRIM:
With ( AB-204 ) in A2, enter =TRIM(MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1)). The result is AB-204. TRIM removes leading and trailing ordinary spaces and reduces repeated internal spaces to single spaces, so do not use it when those spaces are meaningful data.
Choosing MID and FIND or another approach
MID with FIND is a good choice when you need a substring bounded by characters or words, especially when the boundaries move from row to row. It is supported in older Excel releases as well as Microsoft 365, which makes it useful for workbooks shared across different versions. The trade-off is readability: several nested searches can make a formula harder to maintain.
In Microsoft 365 and Excel 2024, TEXTAFTER and TEXTBEFORE can make delimiter-based extraction easier to read. With Batch (AB-204) ready in A2, the following formula returns the part after the opening parenthesis and before the closing one:
With the full string in A2, enter =TEXTBEFORE(TEXTAFTER(A2,"("),")"). The result is AB-204. These newer functions are not available in every older Excel version, so a workbook shared with older installations may need the MID and FIND method instead.
LEFT with FIND is more suitable when you want everything before a delimiter, and RIGHT can be useful for a fixed number of characters at the end. If the data is already separated into columns by a consistent delimiter, Excel’s Text to Columns feature may be easier for a one-time split. For regularly refreshed data with repeated cleanup steps, Power Query can provide a repeatable transformation. Choose based on whether the source is fixed, changes over time, and must work in older Excel versions.
Entering and filling the formula
In Excel for Microsoft 365 on Windows, select the result cell, type the formula, and press Enter. To apply it to further rows, select the cell and drag its fill handle down, or copy it and paste into the target range. A reference such as A2 adjusts to A3, A4, and so on when copied, so each formula reads the corresponding record. If the source is an Excel Table, a formula entered in a table column can fill through that column automatically.
The formula uses commas between arguments, as shown in this article. Some regional Excel settings use semicolons as argument separators; if Excel rejects a correctly copied formula, check the list separator setting for your installation. The function names and extraction logic are the same. The MID and FIND formula also works in Excel for Mac and Excel for the web, although ribbon layout and some surrounding interface details can differ.
For easier maintenance, keep the source text in one column and the extracted value in another instead of overwriting the original. This lets you compare results against source records and correct unusual cases. If the extracted value will be used for lookups or reporting, check that it does not contain hidden spaces or inconsistent punctuation that could make visually similar entries behave differently.
Frequently Asked Questions
What is the MID and FIND formula in Excel?
It is a formula that uses FIND to locate boundaries in text and MID to return the characters between them. For text in A2 with parentheses around the wanted value, use the formula shown at the start of this article.
Does FIND distinguish between uppercase and lowercase?
Yes. FIND is case-sensitive. If you need a case-insensitive search, use SEARCH instead; it performs a similar position search but does not distinguish letter case.
Why does my MID and FIND formula return #VALUE!?
Most often, one of the delimiters is missing, differs in case, or includes unexpected spaces. The error can also occur if the calculated character count is negative. Check the exact source text and the characters used in both searches.
Can MID and FIND extract text after a delimiter?
Yes. Use FIND to locate the delimiter, set MID’s starting position just after it, and provide a character count long enough to cover the remaining text. If you have Microsoft 365 or Excel 2024, TEXTAFTER may be a simpler option for that task.
Is MID the same as FIND in Excel?
No. MID returns characters from a text value; FIND returns the position of one text string inside another. They are often nested together because FIND calculates the positions that MID needs.
Does this formula work when the text between delimiters changes length?
Yes, as long as the expected opening and closing delimiters are present and in the expected order. FIND calculates their current positions for each record, so the extracted substring can be shorter or longer from row to row.
Related
- Mail Merge Excel to Word: Steps, Formatting, and Fixes
- Machine ROI Calculator in Excel: Build and Use One
- Mac Numbers vs Excel: Which Spreadsheet Should You Use?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Table Filter Multiple Values: Methods That WorkTo filter an Excel table for several values, open a column’s filter arrow, clear Select All, and check…
- Excel Calendar Quarter Formula: How to Extract Q1-Q4The simplest Excel formula to get the calendar quarter from a date in cell A1 is =ROUNDUP(MONTH(A1)/3,0). This…
- Calculating Loan Payments in Excel: PMT and ExamplesTo calculate a fixed monthly loan payment in Excel, enter the loan amount, annual interest rate, and term…
- Excel: Sum VLOOKUP Values Across Multiple Matching RowsTo total every row that matches a lookup value, use SUMIF, not a regular VLOOKUP: with the matching…
- Excel Vertical Lookup: VLOOKUP, XLOOKUP, and TipsAn Excel vertical lookup searches down the first column of a table for a value, then returns related…
- How to Filter Negative Values in Excel: Filters & FormulasTo show only negative numbers in Excel, select your data range, choose Data > Filter, open the dropdown…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.