To extract text from the middle of a cell in Excel, use MID: with the source text in A2, =MID(A2,4,5) returns five characters starting at character four. If the position or length varies, use SEARCH or FIND to locate a delimiter, or use TEXTBEFORE and TEXTAFTER in supported Excel versions.
The best formula depends on whether the text always occupies the same character positions or is marked by separators such as hyphens, brackets, or spaces. The examples below use Microsoft Excel for Microsoft 365 on Windows; the basic formulas also work in Excel for Mac and Excel for the web, though newer text functions may not be available in older releases.
How MID extracts characters
MID returns a specified number of characters from a text value. Its arguments are the text to search, the starting position, and the number of characters to return. Excel numbers character positions starting at 1, not 0. For example, in the text ABCDE, A is position 1 and C is position 3.
With the text ID-742QZ-end in A2, this formula returns 742QZ:
Assuming A2 contains ID-742QZ-end, enter =MID(A2,4,5). The result is 742QZ.
The formula does not need to know what the characters mean. It simply counts from the start of the cell, then returns the requested length. That makes MID useful for product codes, fixed-width records, dates stored as text, and other strings with predictable layouts.
Extract text from a fixed position
Use fixed numbers when every cell follows the same pattern. This is often the simplest and clearest approach for imported records where each field has a defined width. For example, if a code always begins with three prefix characters and the next three characters are the value you need, start at position 4 and return 3 characters.
Assuming A2 contains ID-742QZ-end, enter =MID(A2,4,3). The result is 742.
When the required section is farther along in a string, count the characters before it carefully. A temporary test can help: place character positions beside a sample or inspect a short example manually. Remember to include punctuation and spaces in the count. In AB 12345 XY, for instance, the first digit is at position 4 because the two letters and the space come first.
Assuming A2 contains AB 12345 XY and the five digits are required, enter =MID(A2,4,5). The result is 12345.
Fixed-position formulas are easy to read and usually efficient, but they become unreliable if an earlier part of the text can change length. If one record has a longer prefix than another, a fixed starting number may return part of the prefix or omit a character. In that case, find a separator first.
Find a changing position with SEARCH or FIND
SEARCH finds the position of a character or text fragment and is not case-sensitive. FIND performs a similar job but is case-sensitive. Either can provide the start number for MID. You can also use a second search to calculate how many characters lie between two separators.
For example, the code INV-5837-CA has a variable-length value between two hyphens. A formula can find the first hyphen, then find the next one after it, and return the characters in between.
Assuming A2 contains INV-5837-CA, enter =MID(A2,SEARCH("-",A2)+1,SEARCH("-",A2,SEARCH("-",A2)+1)-SEARCH("-",A2)-1). The result is 5837.
The first SEARCH locates the opening hyphen. Adding 1 starts MID at the character immediately after it. The second SEARCH starts looking after the first hyphen, so it finds the closing hyphen. Subtracting the two positions and 1 gives the length of the text between them. This pattern also works when the value has different lengths in different rows, as long as the delimiters are present.
Choose SEARCH for case-insensitive matching, such as locating either ref or REF. Choose FIND when capitalization matters. If the delimiter does not occur in a cell, either function returns an error, so formulas using these functions need appropriate error handling when the data may be inconsistent.
Extract text between brackets or other delimiters
For a short, readable formula in Microsoft 365 or Excel 2024, combine TEXTAFTER and TEXTBEFORE. The first function returns the text after a delimiter; the second returns the text before a delimiter. Nesting them extracts the text between two markers.
Assuming A2 contains Order [AB-204] shipped, enter =TEXTBEFORE(TEXTAFTER(A2,"["),"]"). The result is AB-204.
This formula first discards everything through the opening bracket, leaving AB-204] shipped. TEXTBEFORE then returns the part before the closing bracket. The same method can use parentheses, quotation marks, or another consistent marker by replacing the delimiter text in the formula.
TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024. They are not available in some older Excel releases, where a MID formula with SEARCH is the compatible option. If missing delimiters are expected, an error-handling wrapper can return a blank instead of displaying an error.
Assuming A2 may contain text without brackets, enter =IFERROR(TEXTBEFORE(TEXTAFTER(A2,"["),"]"),""). The result is AB-204 when A2 contains Order [AB-204] shipped, and a blank when the delimiters are missing.
Use IFERROR thoughtfully. It also hides errors unrelated to missing brackets, such as a reference problem. If identifying malformed rows matters, it may be better to show a message such as Check delimiters rather than return an empty cell.
Choose the right extraction method
Before building a formula, decide what defines the middle section. A fixed character count, a position relative to a marker, and a section between two markers call for slightly different approaches.
- Same start and length in every row: use MID with fixed numbers. This is the clearest choice for consistently structured codes.
- Start follows a known marker: use MID with SEARCH or FIND to locate that marker.
- Text sits between two delimiters: use two searches with MID for broad compatibility, or TEXTAFTER inside TEXTBEFORE in Microsoft 365 or Excel 2024.
- Only the beginning or end matters: consider LEFT or RIGHT instead of MID. Those functions are designed for characters counted from one end.
- You need a one-time split rather than a live formula: consider Flash Fill or Text to Columns, especially when the source data is regular.
There is no need to use the newest function just because it is available. A straightforward MID formula can be easier for coworkers to maintain, especially in a workbook that may be opened with an older Excel version. Conversely, TEXTBEFORE and TEXTAFTER can make a delimiter-based formula much easier to audit.
Common errors and practical checks
A formula can return the wrong substring without showing an error, so test it against several representative rows, including the shortest and longest values. Check records with missing separators, extra spaces, or an unexpected prefix. A formula that works for one example may depend on a pattern that does not hold throughout the dataset.
- Wrong starting character: MID counts from 1. Recheck the positions of spaces and punctuation before changing the formula.
#VALUE!from MID: the start position or requested character count may be invalid, such as a negative number of characters. Check the calculated length and the cell reference.#VALUE!or#N/Afrom a search: the delimiter may be absent or typed differently. Look for spaces, alternate punctuation, and inconsistent capitalization.- Unexpected spaces in the result: spaces are characters too. If the source has padding around a value, determine whether those spaces should be kept or removed.
- A number behaves like text: MID returns text, even when the extracted characters look like digits. This is usually suitable for identifiers such as postal codes, where leading zeros matter.
Do not automatically convert extracted digits to numbers. A value such as 00427 may be an identifier, not a quantity; converting it can remove the leading zeros. If you genuinely need a numeric result for arithmetic, convert it deliberately and verify that the value is numeric. Also be cautious with dates: extracting parts of a date stored as text is not the same as using Excel’s date values and date-formatting tools.
Alternatives for repeated or one-time work
If you need to split a column once, Excel’s Text to Columns feature may be faster than filling a formula down. In Excel for Microsoft 365 on Windows, select the cells and choose Data > Text to Columns. Choose a delimiter when the separator is consistent, or fixed width when each field occupies a set number of characters. Review the preview and destination before completing the operation, because the results can overwrite neighboring cells if there is not enough space.
Flash Fill can recognize a pattern from examples and fill the remaining values. Enter one or two correctly extracted results in an adjacent column, then use Data > Flash Fill or the Windows shortcut Ctrl+E. On Mac, the shortcut is typically Control+E; menu availability and shortcut behavior can vary by Excel version. Flash Fill produces values rather than formulas, so later changes to the source cells will not automatically update those results.
For recurring imports or larger cleaning tasks, Power Query can split columns during a repeatable data-preparation process. Its controls are useful when the source data is refreshed regularly and you want the same transformation applied again. For a small table that should update immediately when its source changes, a worksheet formula is usually simpler.
Frequently Asked Questions
What is the Excel formula to extract text from the middle?
Use MID. With the source in A2, =MID(A2,4,5) returns five characters beginning at position 4. Change the start and length to match your text.
How do I extract characters between two hyphens?
Use MID with SEARCH to find each hyphen and calculate the number of characters between them. This works when the text between the hyphens can vary in length, provided both separators exist.
Can Excel extract text between parentheses or brackets?
Yes. In Microsoft 365 or Excel 2024, nest TEXTAFTER and TEXTBEFORE around the opening and closing markers. In older versions, use MID with SEARCH to locate both markers.
Why does MID return the wrong part of my cell?
The start position may be off because Excel counts spaces and punctuation, and starts counting at 1. If the prefix length varies, replace a fixed start number with a position found by SEARCH or FIND.
Does MID return a number or text?
MID returns text. That preserves leading zeros in codes, but a result that looks numeric may need deliberate conversion before it can be used in arithmetic. Keep it as text when it is an identifier.
Does the MID formula work in Excel for Mac?
Yes. MID, SEARCH, and FIND are available in Excel for Mac as well as Excel for Windows. Newer functions such as TEXTBEFORE and TEXTAFTER require Microsoft 365 or Excel 2024.
Related
- How to Insert a Row in Excel Between Existing Data
- How to Unhide Rows in Excel That Won’t Unhide
- Unhide Rows in Excel at Once: A Clear Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Round Excel Without Decimals: Formula or Formatting?To round a value in Excel to the nearest whole number, use =ROUND(A1,0) when the value is in…
- Excel VAT Formula at 20%: Add or Remove VATIf A2 contains a price before VAT, calculate VAT at 20% with =A2*20%; add that VAT to the…
- Excel WORKDAY Function Examples for Real DeadlinesExcel’s WORKDAY function calculates a date a specified number of working days before or after a starting date,…
- Excel Formula Bar Not Showing? How to Restore ItIf the formula bar is missing in Excel for Microsoft 365 on Windows, open the View tab and,…
- Excel VLOOKUP, HLOOKUP, and XLOOKUP: Key DifferencesVLOOKUP searches down the first column of a table, HLOOKUP searches across its first row, and XLOOKUP searches…
- Excel Formula for an Entire Column: How to Use ItTo calculate values from an entire column, use a whole-column reference such as A:A inside a function. To…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.