For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=FUNCTIONS

MID()

Text

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)
→1049

Starts at position 5 and grabs the next 4 characters.

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

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), "-")
→1049

The same result in Microsoft 365. TEXTBEFORE and TEXTAFTER make the nested-FIND pattern above unnecessary on current versions.

Notes and gotchas

  • start_num is 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 formula TEXTBETWEEN replaces on modern Excel.
  • MID returns text, so numeric fragments need VALUE to 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

What goes wrong with MID, and why
You seeBecauseFix
#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.
Tips / Tricks / Hacks

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 tricks and formula patterns
MID patternMID formulaBest useWatch 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.
Common MID mistakes and how to fix them
MID mistakeWhat happens in MIDFix
Off-by-one length including the closing delimiterExtracting 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 reversedMID 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

  1. Parse a list of strings formatted as "Invoice #10452 [Paid]" to extract both the invoice number and the status inside brackets.

Related functions