MID in Excel: Syntax, Examples and Errors
Updated 2026-10-14 · Free · Works in Excel & Google Sheets
Syntax
=MID(text, start_num, num_chars)Quick answer
MID returns characters from anywhere in a cell: =MID(A2, 4, 4) turns "AE-2026-041" into "2026". Give it a start position and a count, and it carves out exactly that slice. Start beyond the text length and you get an empty string; start below 1 or negative count and you get #VALUE!.
Syntax
=MID(text, start_num, num_chars)| Argument | What it means |
|---|---|
| text | The cell or literal holding the text to cut. Required. |
| start_num | Where to begin cutting, counted from 1 — 4 starts at the fourth character. Required, must be 1 or greater; below 1 returns #VALUE!. |
| num_chars | How many characters to take from that position: 4 takes four. Required, must be 0 or greater; negative returns #VALUE!. |
Depending on your regional settings, arguments are separated by semicolons instead of commas: =MID(A2; 4; 4). This is standard in countries where the comma is the decimal separator.
MID counts characters, not bytes and not words: one letter, one digit, one space or one emoji each consume a count. Positions start at 1 — there is no character zero, and MID never reads backwards.
Basic examples
=MID(A2, 4, 4)The fixed-width workhorse: product codes "AE-2026-041" hand over their year "2026", phone numbers give up their area code, and invoice IDs expose their sequence number — any time the meaningful part lives at a known position for a known length.
=MID(A2, FIND("-", A2) + 1, FIND("-", A2, FIND("-", A2) + 1) - FIND("-", A2) - 1)The dynamic upgrade: the inner FIND locates the first dash, the outer FIND starts searching after it to find the second dash, and MID carves out everything between them, so "AE-2026-041" yields "2026" and "US-Q3-772" yields "Q3" — no hard-coded positions.
=MID(A2, SEARCH(" ", A2) + 1, 30)Takes everything after the first space: SEARCH locates the space, +1 skips past it, and 30 (an over-sized count) safely grabs the rest of the cell — "Jane Doe" yields "Doe", "Winston Churchill" yields "Churchill".
=IFERROR(MID(A2, FIND("-", A2) + 1, 4), A2)The production-grade version: rows without a dash would otherwise explode into #VALUE! because FIND returns #VALUE! when the text is missing. Wrapped in IFERROR, those rows simply come back whole instead of breaking the column.
MID returns text even when it carves out digits: MID(123456, 2, 3) gives the text "234", which will not sum. Wrap numeric extractions in VALUE (=VALUE(MID(A2, 4, 4))), and remember dates join as serial numbers — MID of a date carves characters out of the serial, not the display.
Common errors and how to fix them
| Symptom | Most common cause | Fix |
|---|---|---|
| #VALUE! | start_num is below 1, or num_chars is negative — or an embedded FIND/SEARCH cannot locate the delimiter and returns #VALUE! itself. | Use a start of 1 or more and a count of 0 or more, and wrap the delimiter hunt in IFERROR with a fallback. |
| Empty string comes back | start_num exceeds the length of the text — MID reads past the end and returns nothing. | Confirm the text is long enough, or clamp the start with MIN(start_num, LEN(A2)) to stay inside. |
| The cut lands one character early or late | Off-by-one around the delimiter: +1 skips past it, -1 ends before the next one. | Map it out on one row — FIND returns the delimiter position, so FIND(...)+1 begins right after it, and the gap between two FINDs minus 1 is the slice length. |
| Extracted digits will not calculate | MID always returns text, so "007" stays "007" and "234" does not sum. | =VALUE(MID(A2, 4, 4)) converts the carved-out digits into a real number (losing leading zeros, which were text anyway). |
| Leading spaces shift the result | The cell starts with invisible padding, so position 4 is not where you think. | Clean first: =MID(TRIM(A2), 4, 4), or strip whitespace on the whole column in one pass. |
Not an error, but a classic MID behavior: when start_num plus num_chars overshoots the cell length, MID simply returns up to the end — no error, no padding. =MID("Hi", 1, 50) is just "Hi". This makes an over-sized count a safe way to grab "everything from here on".
MID vs. LEFT and RIGHT
| MID | LEFT | RIGHT | |
|---|---|---|---|
| Starts cutting from | Any position you specify | Always the first character | Always the last character |
| Arguments | Text, start position and count | Text and a count | Text and a count |
| Best for | Middle segments — area codes, the part between two delimiters | Prefixes, first names, fixed-width headers | Suffixes, last names, file extensions |
| Dynamic boundaries | Both ends can move — FIND feeds start and count | Only the end can move (via FIND/SEARCH) | Only the start can move (via FIND/SEARCH and LEN) |
MID is the general case: =LEFT(A2, 4) is =MID(A2, 1, 4), and =RIGHT(A2, 4) is =MID(A2, LEN(A2) - 3, 4) when the cell is long enough. LEFT and RIGHT are conveniences for the edges; whenever the slice sits in the middle, MID is the only one of the three that applies.
MID vs. Text to Columns, TEXTBEFORE and TEXTAFTER
| MID (+ FIND/SEARCH) | Text to Columns | TEXTBEFORE / TEXTAFTER (Microsoft 365) | |
|---|---|---|---|
| Output | A live formula in a new column; source untouched | Splits the cells in place, overwriting neighbors if you are not careful | Live formulas: =TEXTBEFORE(A2, "-") and =TEXTAFTER(A2, "-") |
| Delimiter logic | Hand-built with FIND/SEARCH and +/- offsets | Dialog-driven, handles repeated delimiters visually | One argument each — no FIND, no offset math |
| Middle segment between two delimiters | Native — MID carves between two FINDs | Possible but fiddly with consecutive splits | Stack TEXTAFTER(TEXTBEFORE(A2, delim2), delim1) — readable but two functions |
| Available in | Every Excel version and Google Sheets | Every Excel version | Excel 365 / 2021 and later only |
Choose by lifespan: MID formulas for parsing that must survive repeated imports and refreshes, Text to Columns for a one-time reshaping of a finished table, TEXTBEFORE/TEXTAFTER when your Excel is modern enough and the segment hugs a single delimiter on one side.
MID in Google Sheets
- Identical syntax and defaults — every formula above pastes into Sheets unchanged.
- Comma-decimal locales use semicolons: =MID(A2; 4; 4), exactly like Excel.
- FIND stays case-sensitive and SEARCH stays case-insensitive with wildcard support — the pairing behaves the same.
- For pattern-based middle segments Sheets adds REGEXEXTRACT, which can pull "the part between two dashes" in one expression instead of nesting FIND calls.
Limitations and pro tips
- One slice per call: splitting a code into three pieces means three columns — LEFT, MID and RIGHT side by side is the classic parsing layout.
- FIND vs. SEARCH is a deliberate choice: SEARCH for case-insensitive delimiters like a space or dash, FIND when "a" and "A" must mean different targets.
- Wrap every delimiter-driven extraction in IFERROR — a single row missing its separator otherwise spreads #VALUE! through the whole column.
- Numbers stay text after the cut: =VALUE(MID(A2, 4, 4)) is the way back to a calculable number.
- An over-sized num_chars is a feature, not a bug: =MID(A2, SEARCH(" ", A2) + 1, 999) safely grabs "everything after the first space" without measuring the length first.
Think of MID as the middle station of a parsing line: TRIM the padding, locate the first delimiter with SEARCH, locate the second with a nested SEARCH, carve the gap with MID, convert with VALUE when the result is numeric. Each stage stays in its own column, so when the import format shifts next month, only one formula changes.
Related Tools
Related Functions
Frequently Asked Questions
What does the MID function do in Excel?
It returns a slice of characters from anywhere in a cell: =MID(A2, 4, 4) takes four characters starting at position 4. Unlike LEFT and RIGHT, MID needs both a start position and a count, so it can carve out any middle segment.
How do I extract text between two delimiters with MID?
Find both delimiters and carve between them: =MID(A2, FIND("-", A2) + 1, FIND("-", A2, FIND("-", A2) + 1) - FIND("-", A2) - 1). The inner FIND locates the first dash, the outer FIND finds the second, and the difference minus 1 is the slice length.
Why does MID return #VALUE!?
Either start_num is below 1, num_chars is negative, or an embedded FIND/SEARCH located no delimiter — FIND returns #VALUE! when the text is missing, and MID inherits it. Use start ≥ 1, count ≥ 0, and wrap the delimiter hunt in IFERROR with a fallback.
What happens when start_num is larger than the text?
MID returns an empty string — it reads past the end and finds nothing. =MID("Hi", 10, 4) gives "". This is useful for "if there is a suffix, take it; otherwise blank" logic, but it surprises people who expect an error.
What is the difference between MID and LEFT or RIGHT?
MID starts wherever you say and needs a start position plus a count; LEFT always starts at character 1 and needs only a count; RIGHT always starts at the end and needs only a count. MID is the general case — LEFT and RIGHT are conveniences for the edges.
Why do the numbers extracted with MID not calculate?
MID returns text, so its "234" is a label, not a value. Convert the result with =VALUE(MID(A2, 4, 4)) — but note that leading zeros disappear in a real number, since they only existed as text.
Does MID work in Google Sheets?
Yes — same name, same arguments, same FIND/SEARCH pairing and semicolon convention in comma-decimal locales. Sheets additionally offers REGEXEXTRACT for pattern-based extraction of middle segments.
Ready to try it on your own file?
Open Split Text into Columns in Excel — It's Free