MID()
Returns characters from the middle of a text string, starting at a position you specify.
MID(text, start_num, num_chars)MID extracts num_chars characters starting at position start_num, counting from 1 at the left of the string. It's the tool for pulling a chunk out of the middle of consistently formatted text.
If start_num is beyond the end of the text, MID returns an empty string rather than an error.
MID is the right choice when the segment you want is bounded on both sides, and the classic form nests FIND inside itself to locate the second delimiter. That expression is correct but genuinely hard to read, and a year later nobody will want to modify it. On Microsoft 365, TEXTBEFORE and TEXTAFTER express the same extraction in a form you can read aloud. Where the data is repetitive and consistent, Flash Fill or a Power Query split column will often do the whole job without a formula at all.
Arguments
- text
- The text to extract characters from.
- start_num
- The position of the first character to extract, counting from 1.
- num_chars
- How many characters to extract from that position.
Examples
=MID("SKU-1049-BLK", 5, 4)Starts at position 5 and grabs the next 4 characters.
=MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1)-FIND("-", A2)-1)The middle segment between the first and second hyphen. The nested FIND locates the second delimiter by starting its search after the first.
=TEXTAFTER(TEXTBEFORE(A2, "-", 2), "-")The same result in Microsoft 365. TEXTBEFORE and TEXTAFTER make the nested-FIND pattern above unnecessary on current versions.
Notes and gotchas
start_numis 1-based — the first character is position 1, not 0.- A start position beyond the string's length returns an empty string rather than an error.
- The common two-delimiter extraction is
MID(A2, FIND("(",A2)+1, FIND(")",A2)-FIND("(",A2)-1), which is exactly the kind of formulaTEXTBETWEENreplaces on modern Excel. - MID returns text, so numeric fragments need
VALUEto become numbers. - For parsing that has to survive varying formats, Power Query's Column From Examples is more robust than any MID formula.
Common errors
| You see | Because | Fix |
|---|---|---|
| #VALUE! | start_num is less than 1, or num_chars is negative. | A negative length usually means the two FIND positions came back in the wrong order — the delimiters may be missing. |
MID tricks, tips, and workbook hacks
MID extracts a substring from the interior of a text value given a starting position and a character count. It is indispensable for parsing structured smart-codes (like SKU "US-2026-ELC-0049"), IBANs, serial numbers, and delimited strings where the target sits between two markers.
When extracting everything from a middle position to the very end of a string, you don't need to calculate LEN(A2)—just pass a generous constant like 999 as num_chars and MID safely stops at the end of the text.
Quick MID checks before trusting the result
- When pulling between two delimiters, start_num is FirstPos + 1 and num_chars is SecondPos - FirstPos - 1.
- Pass 999 as the 3rd argument when you simply want everything from start_num to the end of the cell.
- Coerce numeric segments with --MID(...) when the extracted segment represents a quantity, year, or department number.
| MID pattern | MID formula | Best use | Watch out |
|---|---|---|---|
| Extract text between parentheses | =MID(A2,FIND("(",A2)+1,FIND(")",A2)-FIND("(",A2)-1) | Pulling a ticker symbol or department code out of strings like "Microsoft Corp (MSFT)". | Subtracting 1 at the end excludes the closing parenthesis. |
| Extract middle token between two hyphens | =MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1) | Parsing the second segment of a 3-part code like "NY-FINANCE-402". | The inner FIND("-", A2)+1 tells the second FIND to start searching after the first hyphen. |
| Pull everything after a multi-character label | =TRIM(MID(A2,SEARCH("Ref:",A2)+4,999)) | Extracting reference numbers from bank memo strings like "ACH Payment Ref: 8849201". | Add the length of the label ("Ref:" = 4) to the starting position. |
| MID mistake | What happens in MID | Fix |
|---|---|---|
| Off-by-one length including the closing delimiter | Extracting between "(" and ")" returns "MSFT)" with a stray parenthesis attached. | Remember that the character distance between pos1 and pos2 is pos2 - pos1 - 1. |
| Passing a negative num_chars when delimiters are reversed | MID immediately throws #VALUE! if the closing marker appears before the opening marker. | Verify both markers exist and appear in the expected order, or wrap in IFERROR. |
MID practice drills
- Parse a list of strings formatted as "Invoice #10452 [Paid]" to extract both the invoice number and the status inside brackets.



