Introduction
The MID Function in Excel extracts a specific number of characters from the middle of a text string. You choose the starting position and the number of characters to return.
It is useful for extracting department codes, years, product identifiers, and employee numbers from structured text. This tutorial focuses entirely on the MID function, with practical examples and exercises to help you understand how it works.
MID Function Syntax
=MID(text, start_num, num_chars)
- text: The text or cell containing the data.
- start_num: The position of the first character to extract.
- num_chars: The number of characters to extract.
Important: Excel starts counting character positions from 1. Spaces, hyphens, and other symbols also count as characters.
Question 1: Extract a Department Code from Employee IDs
Problem Statement
An HR department stores department codes inside employee IDs. Extract the two-character department code from each ID.
| Employee ID | Department Code |
|---|---|
| EMP-HR-1045 | HR |
| EMP-IT-2086 | IT |
| EMP-FN-3172 | FN |
| EMP-MK-4291 | MK |
Assume employee IDs are stored in cells A2.
Excel Formula
=MID(A2,5,2)
Expected Result: HR
Student Task: Add EMP-AC-5834 in cell A6. Extract the two-character department code.
New Expected Result: AC
Question 2: Extract a Year from an Invoice Reference
Problem Statement
An accounts department stores the year in the middle of each invoice reference. Extract the four-digit year.
| Invoice Reference | Year |
|---|---|
| INV-2023-501 | 2023 |
| INV-2024-602 | 2024 |
| INV-2025-703 | 2025 |
| INV-2026-804 | 2026 |
Assume invoice references are stored in cells A2.
Excel Formula
=MID(A2,5,4)
Expected Result: 2023
Student Task: Add INV-2027-905 in cell A6. Extract the year.
New Expected Result: 2027
Question 3: Extract a Product Code from a Reference
Problem Statement
A company uses product references containing a three-character product code in the middle. Extract the product code.
| Product Reference | Product Code |
|---|---|
| ABC123XYZ | 123 |
| DEF456WEB | 456 |
| GHI789APP | 789 |
| JKL234PRO | 234 |
Assume product references are stored in cells A2.
Excel Formula
=MID(A2,4,3)
Expected Result: 123
Student Task: Add MNO567NET in cell A6. Extract the three characters starting at position 4.
New Expected Result: 567
Question 4: Extract an Employee Number from a Complete Employee ID
Problem Statement
A company uses employee IDs in which the employee number always occupies the final four positions. Extract the employee number using MID.
| Employee ID | Employee Number |
|---|---|
| EMP-HR-1045 | 1045 |
| EMP-IT-2086 | 2086 |
| EMP-FN-3172 | 3172 |
| EMP-MK-4291 | 4291 |
Assume employee IDs are stored in cells A2.
Excel Formula
=MID(A2,8,4)
Expected Result: 1045
Student Task: Add EMP-AC-5834 in cell A6. Extract the four-digit employee number.
New Expected Result: 5834
Question 5: Understand Character Positions and Spaces
Problem Statement
A student database contains course codes with spaces. Extract the three characters starting at position 5.
| Course Code | Extracted Text |
|---|---|
| WEB DESIGN | DES |
| PYTHON BASICS | ON |
| DATA ANALYSIS | A A |
Assume the course codes are stored in cells A2.
Excel Formula
=MID(A2,5,3)
Expected Result for WEB DESIGN: DES
Explanation: In WEB DESIGN, the characters are counted as follows:
| Character | W | E | B | Space | D | E | S | I | G | N |
|---|---|---|---|---|---|---|---|---|---|---|
| Position | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
The fifth character is D, so extracting three characters returns DES.
Student Task: Enter EXCEL TRAINING in cell A5. Extract three characters starting at position 7.
New Expected Result: TRA
Question 6: Extract Characters When the Requested Length Exceeds the Remaining Text
Problem Statement
A product list contains codes of different lengths. Extract five characters starting at position 3.
| Product Code | Extracted Text |
|---|---|
| ABCD | CD |
| LAPTOP | PTOP |
| KEYBOARD | YBOAR |
| XY | Empty text |
Assume product codes are stored in cells A2.
Excel Formula
=MID(A2,3,5)
Expected Result for ABCD: CD
Explanation: MID returns the available characters when fewer characters remain than requested. If the starting position is beyond the end of the text, it returns an empty text string.
Student Task: Enter MON in cell A6. Extract five characters starting at position 2.
New Expected Result: ON
Student Tasks: MID Function in Excel
Complete these exercises independently using only the MID function.
Task 1: Extract Department Codes
| Employee ID | Expected Result |
|---|---|
| EMP-HR-101 | HR |
| EMP-IT-202 | IT |
| EMP-FN-303 | FN |
| EMP-MK-404 | MK |
Task: Extract two characters starting at position 5.
Task 2: Extract the Year
| Invoice Reference | Expected Result |
|---|---|
| INV-2022-501 | 2022 |
| INV-2023-602 | 2023 |
| INV-2024-703 | 2024 |
| INV-2025-804 | 2025 |
Task: Extract four characters starting at position 5.
Task 3: Extract Product Codes
| Product Reference | Expected Result |
|---|---|
| ABC456XYZ | 456 |
| DEF789WEB | 789 |
| GHI123APP | 123 |
| JKL654PRO | 654 |
Task: Extract three characters starting at position 4.
Final Student Challenge: Build an Employee ID Summary
Problem Statement
You work in the HR department of a company. The employee database stores the department code and employee number inside a single employee ID. Extract both values into separate columns using only the MID function.
| Employee ID | Department Code | Employee Number |
|---|---|---|
| EMP-HR-1045 | HR | 1045 |
| EMP-IT-2086 | IT | 2086 |
| EMP-FN-3172 | FN | 3172 |
| EMP-MK-4291 | MK | 4291 |
| EMP-AC-5834 | AC | 5834 |
Assume employee IDs are stored in column A, starting from cell A2.
Your Tasks
- Extract the two-character department code into column B.
- Extract the four-digit employee number into column C.
- Use cell references in your formulas.
- Apply both formulas to all employee records using the fill handle.
- Add
EMP-IT-6728and extract its department code and employee number.
Formula for Department Code
=MID(A2,5,2)
Formula for Employee Number
=MID(A2,8,4)
Expected Result for the New Employee
| Employee ID | Department Code | Employee Number |
|---|---|---|
| EMP-IT-6728 | IT | 6728 |
Bonus Challenge: If cell A2 contains EXCEL2026TUTORIAL, write a MID formula to extract 2026.
Final Goal: Build an employee summary that extracts the required information from structured employee IDs using only the MID function.
Key Takeaways
- MID extracts characters from a specified position in text.
- The starting position begins at 1.
- The number of characters determines how much text is returned.
- Spaces and symbols count as characters.
- MID returns the available characters if fewer remain than requested.
- MID returns an empty text string if the starting position is beyond the end of the text.
- MID returns text, even when the extracted characters contain only digits.
Frequently Asked Questions (FAQs)
1. What is the MID function in Excel?
MID extracts a specified number of characters from a text string, starting at a position you choose.
2. What is the syntax of the MID function?
The syntax is =MID(text, start_num, num_chars).
3. Does MID count spaces?
Yes. Spaces, hyphens, and punctuation marks count as characters.
4. Can MID extract numbers from an employee ID?
Yes. MID can extract digits from employee IDs, invoice references, and other text strings.
5. What happens if I request more characters than remain?
MID returns the characters available from the starting position to the end of the text.
6. What happens if the starting position exceeds the text length?
MID returns an empty text string.
7. What is the difference between LEFT, RIGHT, and MID?
LEFT extracts characters from the beginning, RIGHT extracts characters from the end, and MID extracts characters from a specified position.
8. Does MID return a number or text?
MID returns text, even if the extracted characters contain only digits.

