1. Core Concept & 3-Argument Syntax
While LEFT and RIGHT are locked to string edges, MID extracts characters from any arbitrary coordinate within the string:
The string or cell reference (e.g. A2).
The 1-based position of the first character to extract (e.g. 5).
How many total characters to extract forward (e.g. 3).
2. Understanding 1-Based Character Positions
Interactive string position visualizer for "EXCEL-2026". Adjust the start coordinate and character count:
=MID("EXCEL-2026", 7, 4) ➔ Extracted Substring: "2026"3. 🔥 Live Interactive — Basic MID Practice Lab
In the Employee Codes below, characters 5 to 7 represent the city code (EMP-MUM-1024). Edit any employee code to observe real-time extraction:
| Row | Editable Employee Code [Col A] | Formula Executed | Extracted City Code [Col B] |
|---|---|---|---|
| 2 | =MID(A2, 5, 3) | "MUM" | |
| 3 | =MID(A3, 5, 3) | "DEL" | |
| 4 | =MID(A4, 5, 3) | "PUN" | |
| 5 | =MID(A5, 5, 3) | "BLR" |
4. 🔥 Live Practice — Start Position & Count
Extract the 4-digit fiscal year from transaction codes (TXN-2026-001):
| Transaction Code | Formula | Extracted Year |
|---|---|---|
| TXN-2026-001 | =MID("TXN-2026-001", 5, 4) | "2026" |
| TXN-2026-002 | =MID("TXN-2026-002", 5, 4) | "2026" |
| TXN-2026-003 | =MID("TXN-2026-003", 5, 4) | "2026" |
5. Practical Multi-Field SKU Extraction
Decompose composite SKUs (ELE-LAP-001) into clean Category and Product fields:
| Product SKU | Category [=MID(A2, 1, 3)] | Product Code [=MID(A2, 5, 3)] | Description |
|---|---|---|---|
| ELE-LAP-001 | — | — | Laptop 16-inch |
| FUR-CHA-002 | — | — | Office Chair |
| ELE-MON-003 | — | — | Monitor 4K |
| FUR-DES-004 | — | — | Standing Desk |
6. 🔥 Important: Position Stability in Schemas
MID relies on fixed, predictable schemas. In ORD-2026-MUM-015, start_num = 10 and num_chars = 3extracts "MUM":
=MID("ORD-2026-MUM-015", 10, 3) ➔ Extracted: "MUM"7. Edge Cases & Length Overflow
=MID("Excel", 2, 0) ➔ "" (Empty text string).
=MID("Excel", 10, 3) ➔ "" (Empty text string).
=MID("Excel", 0, 3) ➔ #VALUE!
=MID("Excel", 4, 10) ➔ "el" (Available tail).
8. 🔥 Live Debugging & Off-By-One Fixes
Goal: Extract "MUM" from EMP-MUM-1024. Formula: =MID(A2, 5, 2)
num_chars = 3.Goal: Extract "MUM". Formula: =MID(A2, 4, 3)
start_num = 5.9. Real Data-Cleaning Challenge (Customer References)
Extract both the 3-letter city code and the 5-digit customer serial from reference codes:
| Customer Reference | Customer Name | City Code [MID(A2, 5, 3)] | Customer Serial [MID(A2, 9, 5)] |
|---|---|---|---|
| CUS-MUM-00125 | Nikhil Aggarwal | — | — |
| CUS-DEL-00481 | Ritu Sen | — | — |
| CUS-PUN-00892 | Sanjay Jadhav | — | — |
| CUS-BLR-01345 | Divya Nambiar | — | — |
10. 🔥 Live Business Scenario (Invoice Audit)
Audit invoices by parsing Year (characters 5–8) and Region (characters 10–12):
| Invoice Code | Fiscal Year [MID(A2, 5, 4)] | Region Code [MID(A2, 10, 3)] | Amount ₹ |
|---|---|---|---|
| INV-2026-MUM-1045 | — | — | ₹84,000 |
| INV-2026-DEL-2048 | — | — | ₹42,000 |
| INV-2026-PUN-3091 | — | — | ₹65,000 |
| INV-2026-BLR-4012 | — | — | ₹98,000 |
11. MID vs. LEFT vs. RIGHT Comparison
Consider the unified string "EMP-MUM-1024":
| Function | Formula | Extracted Result | Role |
|---|---|---|---|
| LEFT | =LEFT(A2, 3) | "EMP" | Prefix / Beginning |
| MID | =MID(A2, 5, 3) | "MUM" | Middle / Coordinate-based |
| RIGHT | =RIGHT(A2, 4) | "1024" | Suffix / End |
12. Important Limitations & Variable Delimiters
MID requires a fixed numeric start coordinate. If preceding tokens have variable lengths (e.g. PRD-IND-LAP vs PRD-UK-LAP), hardcoded numbers like =MID(A2, 9, 3) will drift and capture incorrect characters. Dynamic parsing requires pairing with FIND/SEARCH or modern functions like TEXTBEFORE/TEXTAFTER.
13. 🔥 Live Final Challenge (Global Product Codes)
Extract Country and Product codes from global inventory references:
| Product Reference | Country Code [MID(A2, 5, 3)] | Product Type | Price ₹ |
|---|---|---|---|
| PRD-IND-LAP-1025 | — | — | ₹65,000 |
| PRD-USA-MON-2048 | — | — | ₹22,000 |
| PRD-IND-KBD-3091 | — | — | ₹4,500 |
| PRD-UK-CHA-4012 | — | — | ₹8,500 |
14. Quick Check & Knowledge Assessment
Knowledge Assessment: Excel MID Function
Test your understanding of mid-string slicing, parameter validation, coordinate counting, and delimiter limitations.
1. What is the fundamental purpose of the Microsoft Excel MID function?
15. Accuracy & Official Specification
- Signature:
=MID(text, start_num, num_chars). - Position Rules:
start_num ≥ 1. Values < 1 return#VALUE!. - Character Count:
num_chars ≥ 0. Negative values return#VALUE!. - Boundaries: If
start_num > LEN(text), MID returns""(empty string).