MID — Extract from the Middle
The third and most flexible of the text-extraction trio. Where LEFT pulls from the start and RIGHT pulls from the end, MID pulls from a specific POSITION in the middle. Three arguments — text, start position, and number of characters — give you surgical control over exactly which slice you want.
start_num uses 1-based indexing. Position 1 is the FIRST character — NOT position 0 like most programming languages. So MID("REG-WA", 1, 3) returns "REG" (the first three characters). If you pass MID("REG-WA", 0, 3) you get a VALUE error. If you're coming from JavaScript, Python, or any zero-indexed language, this trips you up once and then you remember forever.The position ruler — MID's mental model
MID is the only one of the trio that needs a start position. Understanding positions visually is worth a thousand words. Here's REG-WA-2026-001 mapped character-by-character, with three MID formulas highlighted below:
REG-WA-2026-001
Reading the ruler: the position number tells you WHERE MID starts. The second argument (num_chars) tells you HOW MANY to take from there. So MID("REG-WA-2026-001", 5, 2) starts at position 5 (the "W"), grabs 2 characters, and returns "WA". The colored bar shows exactly which characters get pulled.
Syntax breakdown
MID takes three arguments — all required. Unlike LEFT and RIGHT (2 args, second optional), MID needs you to specify both where to start and how much to take.
| Argument | Type | What it does |
|---|---|---|
text |
REQUIRED | The source string. Cell reference, literal text in quotes, number (converted to text), or the result of another function. |
start_num |
REQUIRED | The position where extraction begins. 1-indexed: position 1 = first character. Must be at least 1. Must not exceed the string length or MID returns an empty string. |
num_chars |
REQUIRED | How many characters to take starting from start_num. Must be zero or positive. If greater than remaining string length, MID returns everything from start_num to the end (no error). |
LEN(A2) or just 999). MID returns as much as exists — never errors from asking for too much.
Five working examples
Every example uses the 10-row structured-text table from the workbook.
01 Fixed positions — parse a structured code
Product codes have a rigid structure: type at 1-3, state at 5-6, year at 8-11, sequence at 13-15. MID extracts any slice you want.
REG-WA-2026-001 — start at 5, take 2| Product Code | State (MID 5,2) | Year (MID 8,4) | Sequence (MID 13,3) |
|---|---|---|---|
| REG-WA-2026-001 | WA | 2026 | 001 |
| REG-CA-2026-002 | CA | 2026 | 002 |
| INT-EU-2026-003 | EU | 2026 | 003 |
| REG-CA-2026-004 | CA | 2026 | 004 |
| INT-AS-2026-005 | AS | 2026 | 005 |
MID lets you pull any component — even ones LEFT and RIGHT can't reach (like a piece from the middle). Once separated, each column can be sorted, filtered, or aggregated independently.
02 Phone exchange — the middle 3 digits
Phone 512-555-0142 has an area code (LEFT 3), exchange (MID 5,3), and line number (RIGHT 4). MID handles the middle.
Grouping by exchange
03 MID as a "LEFT-with-offset"
MID(text, 1, n) is exactly equivalent to LEFT(text, n). Sometimes you want to skip the first few chars — that's MID(text, k, n).
=LEFT(B2, 3). MID with start=1 IS LEFT.Skip the first 4 chars, take the rest
When to reach for this pattern: when you want "everything after position N" without knowing the end length. Give MID a huge num_chars (LEN of the whole string works, or just 999) and it grabs to the end.
04 MID + FIND — dynamic start position
When the position where you want to start VARIES from row to row. FIND gives you the position; MID uses it dynamically.
Everything after the "@" in emails
emma.thompson@acmecorp.comReading the formula: FIND("@", D2) returns the position of "@" — say 14. Add 1 so MID starts at position 15 (right after the "@"). LEN(D2) is used as num_chars — bigger than what remains, so MID just grabs to the end.
| FIND("@") | Domain (MID) | |
|---|---|---|
| emma.thompson@acmecorp.com | 14 | acmecorp.com |
| d.kim@acmecorp.com | 6 | acmecorp.com |
| sofia.r@acmecorp.com | 8 | acmecorp.com |
| mchen@acmecorp.com | 6 | acmecorp.com |
05 MID + double FIND — extract between two delimiters
The trickiest but most valuable MID pattern. When what you want sits BETWEEN two delimiters, use FIND twice — once for the start, once for the end.
Extract the state from an unstructured product code
Imagine product codes without fixed lengths — REG-WA-2026-001, REG-CALIFORNIA-2026-001, INT-EU-2026-001. Fixed-position MID breaks. Two FINDs save you.
Reading this beast:
FIND("-", B2)finds the first dash. Say position 4. Add 1 so MID starts at 5.FIND("-", B2, FIND("-", B2) + 1)finds the SECOND dash — by starting a new FIND at position 5. Say position 7.- Subtract the first dash position and 1 more:
7 - 4 - 1 = 2. That's the number of chars between the dashes. - Result: MID starts at 5, takes 2 chars →
"WA".
=TEXTBEFORE(TEXTAFTER(B2, "-"), "-") does the same thing — cleaner but modern-only. See the alternatives section below.
Interactive playground
Try it Live MID demonstration
Mirrors live cells from the workbook. Edit the yellow inputs → the blue answer updates.
Download the workbook to try the position ruler, MID+FIND, and MID+double-FIND live.
The MID cheat sheet — 10 patterns for daily use
Copy any of these, adapt to your data, and you're 80% of the way there:
10 patterns you'll use every day
From simplest fixed-position pulls to dynamic MID+FIND combos.
The Text-Extract family — three ways to slice
Meet the family
Excel has three foundational text-slicing functions. Each pulls from a different position. This page is MID — the most flexible, since it can start anywhere.
MID is a superset of LEFT and RIGHT in raw capability — MID(text, 1, n) equals LEFT(text, n), and MID(text, LEN(text)-n+1, n) equals RIGHT(text, n). But LEFT and RIGHT are more readable for their common cases. Reach for MID when your extraction genuinely starts in the middle.
Excel 365 alternative — TEXTSPLIT
Both return the second segment of a dash-delimited string. TEXTSPLIT breaks the string into an array; INDEX picks item 2. Compact syntax that's hard to argue with — but only on modern Excel.
Common errors and how to fix them
MID's 1-indexed start position causes most of its bugs. Six common scenarios:
| Result | Why it happens | Broken → Fix |
|---|---|---|
| VALUE error | start_num was 0 or negative. Position 1 is the minimum. | =MID(A2, 0, 3) // VALUE error
=MID(A2, 1, 3) // position 1 = first char |
| Empty result | start_num exceeded the string length — MID returns "". | =MID("Hi", 10, 3) // returns ""
Check LEN first, or accept empty as valid output |
| Off-by-one | Forgot the "+1" after FIND when starting after a delimiter. | =MID(A2, FIND("@", A2), LEN(A2)) // includes the "@"
=MID(A2, FIND("@", A2) + 1, LEN(A2)) |
| Includes delimiter | num_chars extended past the closing delimiter into what should be excluded. | Wrong length in double-FIND math
Subtract 1 for the delimiter position: end - start - 1 |
| FIND errors | The delimiter you searched for doesn't exist in some rows. | =MID(A2, FIND("@", A2)+1, LEN(A2)) // errors on "no email"
=IFERROR(MID(A2, FIND("@", A2)+1, LEN(A2)), "") |
| Whitespace | Leading spaces shifted the positions you expected. | =MID(" REG-WA", 5, 2) // "G-" not "WA"
=MID(TRIM(A2), 5, 2) |
Related functions
Complementary functions
Excel version compatibility
MID has been in Excel since version 1.0. Every platform supports it identically:
| Platform | Supports MID? | Notes |
|---|---|---|
| Excel 365 (Windows & Mac) | ✓ Yes | Full support |
| Excel 2021 / 2019 / 2016 / 2013 / 2010 / 2007 / 2003 | ✓ Yes | Full support |
| Excel for the web | ✓ Yes | Full support |
| Excel on iPad & iPhone | ✓ Yes | Full support |
| Google Sheets | ✓ Yes | Same syntax |
| LibreOffice Calc | ✓ Yes | Full support |
| Apple Numbers | ✓ Yes | Full support |
MIDB counts BYTES instead of characters — for double-byte character sets (Japanese, Chinese, Korean) at the byte level. Regular MID handles Unicode correctly for most uses.
When to use MID vs. alternatives
Use MID when…
- The piece you want is in the MIDDLE. Not the start, not the end.
- You know both the start position and the length. Fixed-structure codes, phone exchanges, timestamp fields.
- You need dynamic start positions with FIND. "Everything after the delimiter" is a natural MID+FIND pattern.
- You need universal Excel support. Works everywhere.
Use LEFT instead when…
- The piece starts at position 1. LEFT reads better than
MID(text, 1, n).
Use RIGHT instead when…
- The piece runs to the end of the string. RIGHT is cleaner than
MID(text, LEN-n+1, n).
Use TEXTSPLIT / TEXTAFTER / TEXTBEFORE (Excel 365) instead when…
- You're on Excel 365 and don't need backward compatibility.
- Your extraction involves multiple delimiters — modern functions handle these cleanly.
- You want the cleanest possible syntax.
How MID actually works
The algorithm
MID walks the source string to position start_num (1-indexed), then reads up to num_chars characters from there. If start_num is past the string end, MID returns an empty string. If num_chars extends past the string end, MID returns everything from start_num to the actual end — no error.
The 1-indexed convention
Position 1 is the FIRST character. Position 2 is the second. This is intuitive from a natural-language perspective ("the 5th character") but confusing for programmers used to zero-indexed strings. If you write MID(text, 0, 3) expecting to get "the first 3 characters", you'll get a VALUE error instead. Use position 1 for that (or just use LEFT).
Number and date handling
Numbers become their default text representation. Dates become serial numbers. Wrap in TEXT() to preserve formatting: =MID(TEXT(A2, "yyyy-mm-dd"), 6, 2) extracts the month portion from a formatted date.
The "grab to the end" trick
Since num_chars greater than the remaining string just returns whatever's there (no error), you can safely pass a very large num_chars to get "everything from start_num onward." Common patterns use LEN(text) or a literal like 999.
Performance notes
MID is fast. Similar performance to LEFT and RIGHT — near-zero overhead for typical use.
- MID+FIND slightly slower. FIND does a linear string scan per call.
- Double-FIND is slower still. Two full-string scans per row. Still fast, but consider TEXTSPLIT on Excel 365 for large data.
- Array MID patterns. The "every Nth char" array formula scales linearly with string length. Fine for short strings, avoid for long ones.
How to write a MID from scratch
-
Identify the source and the piece you want
Where does the piece start? How long is it? Sketch the position ruler if it helps.
-
Determine the start position
Count characters starting at 1 (not 0). For "REG-WA-2026", the "W" is at position 5.
-
Determine num_chars
How many characters after start_num do you want? "WA" is 2. Use LEN or a large number if you want to the end.
-
Fixed positions → hard-code numbers
=MID(A2, 5, 2). Simple, readable, works when data is perfectly structured. -
Variable positions → use FIND
=MID(A2, FIND("delim", A2) + 1, LEN(A2)). Dynamic start, grabs to end. -
Guard with IFERROR when data may vary
=IFERROR(MID(A2, FIND("@", A2)+1, LEN(A2)), ""). Returns empty string when delimiter is missing.
Functions used with MID
Frequently asked questions
What's the difference between MID and LEFT/RIGHT?
LEFT extracts from the START of a string, RIGHT from the END, MID from a specific POSITION in the middle. MID is the most flexible — it can replicate LEFT with start=1 or RIGHT with the right start position math — but LEFT and RIGHT are cleaner for their common cases.
Why does MID start at position 1, not 0?
Excel's text functions use 1-based indexing throughout — position 1 is the first character. This differs from most programming languages (JavaScript, Python, C) which are 0-based. Passing 0 as start_num returns a VALUE error, not the first character.
What happens if start_num is past the string length?
MID returns an empty string — no error. So MID("Hi", 10, 3) returns "". This lets you use MID in conditional formulas without worrying about position overflow.
What if num_chars is bigger than what remains?
MID returns whatever exists from start_num to the actual end of the string. No error. This is the basis of the "grab to the end" trick — pass a big num_chars like LEN(text) or 999 to get everything past your start position.
How do I extract text between two delimiters?
The classic pattern is =MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1) - FIND("-", A2) - 1). Uses FIND twice — once for the start delimiter, once for the end. Ugly but universal. On Excel 365, =INDEX(TEXTSPLIT(A2, "-"), 2) is much cleaner.
Does MID work in Google Sheets?
Yes, identically. Same syntax in Google Sheets, LibreOffice, and Apple Numbers.
Can MID replace LEFT and RIGHT?
Functionally yes: MID(text, 1, n) equals LEFT(text, n), and MID(text, LEN(text)-n+1, n) equals RIGHT(text, n). But LEFT and RIGHT read better for their common cases. Save MID for actual middle extraction.
Can MID return a number?
No — always text. Even "123" extracted from "abc-123-xyz" is text. Wrap in VALUE() or double-negative (--) to convert: =VALUE(MID(A2, 5, 3)).
How do I extract the Nth word?
Combine MID with SUBSTITUTE to replace the Nth space with a marker, then FIND the marker. Ugly. On Excel 365, use TEXTSPLIT: =INDEX(TEXTSPLIT(A2, " "), 3) gets the third word.
Why does my MID return the wrong characters?
Almost always position math is off by one. Remember: position 1 = first character. If your source starts at position 5, you count "5, 6, 7..." not "5, 6, 7, 8" for 3 chars. And when using FIND for dynamic starts, don't forget the "+1" to skip past the delimiter.
Should I use MID+FIND or TEXTSPLIT?
TEXTSPLIT is cleaner for delimiter-based splits, but Excel 365 only. MID+FIND works universally but gets ugly for anything beyond simple cases. If you're on 365 and don't share workbooks with older Excel, TEXTSPLIT wins.
Which templates use MID?
Templates that parse structured codes with meaningful middle segments. Prominently: Invoice (invoice-year segment from ID), Sales Dashboard (region code from product SKU), KPI Dashboard (period identifier), Employee Attendance (department code from badge), Timesheet (project year from task ID), Inventory Tracker (warehouse code from SKU), and Expense Report (fiscal quarter from expense ID).
Templates that use MID
7 of our 10 templates use MID for parsing middle segments from structured codes:
Skip the syntax. Ask in plain English.
The Sheets & Cells AI Add-in writes MID, MID+FIND, and every text-extraction pattern — right inside Excel. Type "get the year from this code" and get the working formula, ready to paste.
Try the AI Add-in →