Introduction to MID Function in Excel
Need to extract just part of a text string in Excel? 🔍 The MID function in Excel helps you pull out a specific number of characters from a cell — starting at any position you define. It’s ideal for handling structured data, cleaning imports, and even formatting names, IDs, or codes.
This guide will show you how to use the MID function like a pro, complete with step-by-step examples and platform-specific keyboard shortcuts 💡.
What Is the MID Function?
The MID function extracts a specific number of characters from a text string, beginning at any position you choose.
Think of it like slicing a string of text:
- Start at character n
- Extract x characters from that point
✅ Great for structured data like:
- Email usernames
- Part numbers
- Phone segments
Syntax of the MID Function
=MID(text, start_num, num_chars)
text: The text string or cell referencestart_num: The position to begin extraction (first character is 1)num_chars: The number of characters to extract
Example:
=MID("ExcelMaster", 6, 6)
Returns: "Master"
Simple MID Example in Excel
| A | B |
|---|---|
| John_Doe_123 | =MID(A1, 6, 3) → Doe |
| AB-2024-XYZ | =MID(A2, 4, 4) → 2024 |
Extracts parts of structured strings 🔢.
Extracting First, Middle, and Last Names
Suppose A2 contains:John William Doe
To extract:
- First Name:
=LEFT(A2, FIND(" ", A2)-1) - Middle Name:
=MID(A2, FIND(" ", A2)+1, FIND(" ", A2, FIND(" ", A2)+1) - FIND(" ", A2) -1) - Last Name:
=RIGHT(A2, LEN(A2) - FIND("~", SUBSTITUTE(A2, " ", "~", 2)))
These formulas dynamically find spaces and extract accordingly 📍.
Use MID with Fixed Text Positions
If your string has a consistent format like "INV-2024-98765":
=MID(A1, 9, 5)
👉 Returns "98765" — the invoice number
📌 Useful when positions never change.
Dynamic Text Extraction with MID
When positions vary, combine MID with SEARCH() or FIND():
=MID(A2, SEARCH("-", A2)+1, LEN(A2))
This formula finds the dash and extracts everything after it.
MID with SEARCH or FIND Functions
Combine MID with FIND to extract text between symbols:
=MID(A2, FIND("-", A2)+1, FIND(":", A2) - FIND("-", A2) -1)
📥 Use case: extract username from email-like text john-doe:info
Using MID in Data Cleaning Tasks
Clean up formats by:
- Extracting standard parts
- Removing unwanted prefixes or suffixes
- Rebuilding cleaner strings
✅ Works well on messy imports or downloaded CSV files.
Combining MID with LEFT or RIGHT
For complex slicing:
=LEFT(MID(A2, 5, 10), 4)
This pulls 10 characters from position 5, then gets only the first 4 — useful for nested extraction.
Extract Specific Parts of IDs or Codes
Example: SKU AB-456-789
To get the second segment:
=MID(A2, FIND("-", A2)+1, FIND("-", A2, FIND("-", A2)+1) - FIND("-", A2) -1)
⚙️ Great for structured data in e-commerce, logistics, and finance.
MID for Email, Phone Numbers, Product Codes
Examples:
- Extract domain from email:
=MID(A2, FIND("@", A2)+1, LEN(A2)) - Extract last 4 digits of phone:
=RIGHT(A2, 4) - Pull product category from SKU:
=MID(A2, 1, 3)
MID and LEN for Flexible Text Lengths
To dynamically adjust length:
=MID(A2, 5, LEN(A2)-4)
Useful when you want everything after the first 4 characters, no matter the string length.
MID with IF Statements
Add logic to your extraction:
=IF(ISNUMBER(FIND("-", A2)), MID(A2, FIND("-", A2)+1, 5), "")
Conditionally extract only if - exists — helps avoid errors 🔧.
Using MID Inside Nested Formulas
Pair with TEXT, VALUE, or SUBSTITUTE:
=VALUE(MID(A2, 6, 4))
This extracts and converts part of a string into a number 🔢.
MID for Consistent Data Formatting
Standardize:
- Contact lists
- Form entries
- Imported database fields
📋 MID ensures uniformity for names, codes, or categories.
Excel Shortcuts: Windows
| Task | Shortcut |
|---|---|
| Insert function | Shift + F3 |
| Edit cell | F2 |
| Fill formula down | Ctrl + D |
| Confirm formula | Enter |
Excel Shortcuts: macOS
| Task | Shortcut |
|---|---|
| Insert function | Shift + Fn + F3 |
| Edit formula | Control + U |
| Confirm formula | Command + Return |
| Fill down | Command + D |
Excel Shortcuts: Ubuntu/Linux
| Task | Shortcut |
|---|---|
| Insert function | Ctrl + F2 |
| Edit active cell | F2 |
| Fill formula down | Ctrl + D |
| Recalculate workbook | Ctrl + Shift + F9 |
Troubleshooting Common MID Errors
| Error | Cause | Fix |
|---|---|---|
#VALUE! | Invalid text or number in arguments | Ensure inputs are text/numbers |
| Wrong output | Incorrect start or length parameters | Double-check FIND/SEARCH |
| Blank cell | Start number exceeds text length | Use LEN() to verify size |
MID vs LEFT vs RIGHT
| Function | Purpose |
|---|---|
| MID | Extract from any position |
| LEFT | Extract from the beginning |
| RIGHT | Extract from the end |
🔁 Combine all three for maximum text flexibility!
FAQs About MID Function in Excel
What does MID do in Excel?
It extracts a specific number of characters from the middle of a text string.
Can I use MID with numbers?
Yes, but numbers are treated as text. Wrap in TEXT() if needed.
What if I want to extract after a symbol?
Use FIND() or SEARCH() with MID.
Is MID case-sensitive?
No — MID just pulls characters; it doesn’t change their case.
Can I use MID in Google Sheets?
Yes! The function works the same way in Google Sheets.
Final Thoughts on MID Function in Excel
The MID function in Excel is your go-to tool for extracting parts of a string — whether you’re working with names, codes, emails, or structured entries. When paired with other text functions like FIND, LEN, or IF, it becomes incredibly powerful and versatile 💪.
Keep your data clean, clear, and consistent with the power of MID() — and speed it all up with the right keyboard shortcuts 🚀.
Complete List of Windows Keyboard Shortcuts
If you need help for Windows, you can find a whole list of all keyboard shortcuts here.
https://keyboard-shortcuts.org/
Advertisment