MS Excel: MID Function, extracting Text from the Middle Like a Pro
- Fakhriddinbek
- Apr 25
- 2 min read
The MID function in Excel is a powerful text function that lets you extract a specific number of characters from any position within a string—not just the beginning or end. It's incredibly useful when your data is embedded in the middle of a text value, such as codes, dates, or combined fields.
This guide will take you through everything from the basics to advanced use cases of the MID function, complete with examples.
Syntax:
MID(text, start_num, num_chars)
text – The original text string.
start_num – The position of the first character you want to extract.
num_chars – The number of characters to extract.
Beginner Level: Basic Usage
Example 1: Extract Specific Characters from the Middle
A (Code) | B (Middle 3 Characters) |
PRD12345 | =MID(A2, 4, 3) → "123" |
ABC98765 | =MID(A3, 4, 3) → "987" |
This formula starts at the 4th character and extracts 3 characters.
Intermediate Level: Dynamic Start with FIND
The real power of MID comes when you combine it with FIND to locate where to start.
Example 2: Extract Text Between Dash and Underscore
A (Value) | B (Extracted) |
ID-456_TB | =MID(A2,FIND("-",A2)+1, FIND("_",A2)-FIND("-",A2)-1) → "456" |
REF-999_OK | =MID(A3,FIND("-",A3)+1, FIND("_",A3)-FIND("-",A3)-1) → "999" |
Perfect for extracting values between symbols!
Advanced Level: MID with LEN, IFERROR, and SEARCH
When dealing with inconsistent data or uncertain formats, MID really shines in combination with other functions.
Example 3: Extract Username from Email with SEARCH
A (Email) | B (Username) |
=MID(A2,1,SEARCH("@",A2)-1) → "john.doe" | |
=MID(A3,1,SEARCH("@",A3)-1) → "emily_b" |
SEARCH("@",A2) finds the @ sign, then we extract all characters before it.
Real-Life Use Cases
Task | Formula Example |
Extract middle digits from code | =MID(A2, 4, 4) (e.g., "PRD2023123" → "2023") |
Get date from timestamp | =MID(A2, 1, 10) (e.g., "2024-06-15 14:30" → "2024-06-15") |
Pull values between two symbols | =MID(A2,FIND("(",A2)+1, FIND(")",A2)-FIND("(",A2)-1) |
Summary Table
Level | Key Feature | Example Formula |
Beginner | Extract fixed characters from middle | =MID(A2, 4, 3) |
Intermediate | Dynamic MID using FIND | =MID(A2,FIND("-",A2)+1, FIND("_",A2)-FIND("-",A2)-1) |
Advanced | Combine with SEARCH, IFERROR, LEN | =MID(A2,1,SEARCH("@",A2)-1) |
Conclusion
The MID function is one of Excel’s most flexible tools for extracting data. Unlike LEFT and RIGHT, it lets you reach right into the middle of any string and grab exactly what you need. Whether you're parsing codes, extracting values between symbols, or pulling structured data out of longer strings, mastering MID will help you clean and analyze your data with confidence.
Comments