Milestone 1: Text Functions 30 min interactive comprehensive guide🎯 Mid-String Slicing & Coordinate Parsing

Excel MID Function: Mid-String Character & Substring Extraction

Extract embedded years, regional tokens, and product codes from any internal position of a string. Master 1-based character indexing, coordinate multi-field extractions, debug off-by-one errors, and analyze structured invoice codes.

⏱️ Estimated Time:30 Minutes
🎯 Level:Substring Parsing & Text Tokenization
📊 Track:Excel Text Functions (Milestone 1)
✨ Mode:Live Interactive Excel Worksheet Lab
01

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:

=MID(text, start_num, num_chars)
1. text:

The string or cell reference (e.g. A2).

2. start_num:

The 1-based position of the first character to extract (e.g. 5).

3. num_chars:

How many total characters to extract forward (e.g. 3).

02

2. Understanding 1-Based Character Positions

Interactive string position visualizer for "EXCEL-2026". Adjust the start coordinate and character count:

E#1
X#2
C#3
E#4
L#5
-#6
2#7
0#8
2#9
6#10
start_num:
num_chars:
Formula: =MID("EXCEL-2026", 7, 4) ➔ Extracted Substring: "2026"
03

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:

RowEditable Employee Code [Col A]Formula ExecutedExtracted 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"
04

4. 🔥 Live Practice — Start Position & Count

Extract the 4-digit fiscal year from transaction codes (TXN-2026-001):

start_num: 5num_chars: 4
Transaction CodeFormulaExtracted 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"
05

5. Practical Multi-Field SKU Extraction

Decompose composite SKUs (ELE-LAP-001) into clean Category and Product fields:

Product SKUCategory [=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
06

6. 🔥 Important: Position Stability in Schemas

MID relies on fixed, predictable schemas. In ORD-2026-MUM-015, start_num = 10 and num_chars = 3 extracts "MUM":

Test Consistent Schema:
Formula: =MID("ORD-2026-MUM-015", 10, 3) ➔ Extracted: "MUM"
07

7. Edge Cases & Length Overflow

1. num_chars = 0:

=MID("Excel", 2, 0) ➔ "" (Empty text string).

2. start_num > Length:

=MID("Excel", 10, 3) ➔ "" (Empty text string).

3. start_num < 1:

=MID("Excel", 0, 3) ➔ #VALUE!

4. Length Overflow:

=MID("Excel", 4, 10) ➔ "el" (Available tail).

08

8. 🔥 Live Debugging & Off-By-One Fixes

Logical Count Truncation

Goal: Extract "MUM" from EMP-MUM-1024. Formula: =MID(A2, 5, 2)

Issue: Extracts only "MU". Fix by setting num_chars = 3.
Off-By-One Start Coordinate

Goal: Extract "MUM". Formula: =MID(A2, 4, 3)

Issue: Includes the hyphen ("-MU"). Fix by changing start_num = 5.
09

9. Real Data-Cleaning Challenge (Customer References)

Extract both the 3-letter city code and the 5-digit customer serial from reference codes:

Customer ReferenceCustomer NameCity Code [MID(A2, 5, 3)]Customer Serial [MID(A2, 9, 5)]
CUS-MUM-00125Nikhil Aggarwal——
CUS-DEL-00481Ritu Sen——
CUS-PUN-00892Sanjay Jadhav——
CUS-BLR-01345Divya Nambiar——
10

10. 🔥 Live Business Scenario (Invoice Audit)

Audit invoices by parsing Year (characters 5–8) and Region (characters 10–12):

Invoice CodeFiscal 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

11. MID vs. LEFT vs. RIGHT Comparison

Consider the unified string "EMP-MUM-1024":

FunctionFormulaExtracted ResultRole
LEFT=LEFT(A2, 3)"EMP"Prefix / Beginning
MID=MID(A2, 5, 3)"MUM"Middle / Coordinate-based
RIGHT=RIGHT(A2, 4)"1024"Suffix / End
12

12. Important Limitations & Variable Delimiters

⚠️ Variable-Length Limitation

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

13. 🔥 Live Final Challenge (Global Product Codes)

Extract Country and Product codes from global inventory references:

Product ReferenceCountry Code [MID(A2, 5, 3)]Product TypePrice ₹
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

14. Quick Check & Knowledge Assessment

TEST YOUR KNOWLEDGE

Knowledge Assessment: Excel MID Function

Test your understanding of mid-string slicing, parameter validation, coordinate counting, and delimiter limitations.

Question 1 of 6Current Score: 0 / 0
Q1

1. What is the fundamental purpose of the Microsoft Excel MID function?

15

15. Accuracy & Official Specification

Official Microsoft Excel Standard:
  • 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).