The MID function in Excel extracts characters from the middle of a text value. It is useful when you need to pull part of a code, ID number, invoice number, product SKU, phone number, or any other text string where the information you need starts at a specific character position.
In This Guide, You’ll Learn
- What the MID function does in Excel
- How to write the MID function syntax
- What each MID argument means
- How to extract characters from the middle of text
- Common MID mistakes to avoid
- Real-world business examples using employee IDs, SKUs, and invoice numbers
What is the MID Function?
The MID function in Excel returns a specific number of characters from the middle of a text string. You tell Excel where to start and how many characters to return.
MID Function Syntax
=MID(text, start_num, num_chars)Argument Definitions
- text: The text string you want to extract characters from.
- start_num: The position of the first character you want to extract.
- num_chars: The number of characters you want Excel to return.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> MID Function Data
Extract Text from the Middle of a Product Code
In this example, each product code contains a department code in the middle. The MID function can pull out that department code.
Formulas Used
=MID(A2,5,3)=MID(A3,5,2)=MID(A4,5,3)=MID(A5,5,3)
How This Formula Works
- Excel looks at cell A2
- Starts at character 5
- Returns 3 characters: FIN
Extract an Employee Department Code
MID is helpful when employee IDs include embedded department information.
Formulas Used
=MID(A2,5,3)=MID(A3,5,3)=MID(A4,5,2)=MID(A5,5,2)
How This Formula Works
- Excel looks at cell A2
- Starts at character 5
- Returns 3 characters: SAL
This can be useful when employee IDs are used to identify departments, regions, job roles, or locations.
Extract the Year from an Invoice Number
MID can also pull dates or years from invoice numbers when the format is consistent.
Formulas Used
=MID(A2,5,4)=MID(A3,5,4)=MID(A4,5,4)=MID(A5,5,4)
How This Formula Works
- Excel looks at cell A2
- Starts at character 5
- Returns 4 characters: 2024
This is useful for reporting, sorting invoices by year, or building helper columns for analysis.
Extract Part of a Phone Number
If phone numbers are stored in a consistent format, MID can extract the middle section.
Formulas Used
=MID(A2,5,3)=MID(A3,5,3)=MID(A4,5,3)=MID(A5,5,3)
How This Formula Works
- Excel looks at cell A2
- Starts at character 5
- Returns 3 characters: 555
Extract a Location Code from a SKU
Many businesses use SKU numbers that contain important information like product type, size, region, or warehouse location.
Formulas Used
=MID(A2,5,3)=MID(A3,5,3)=MID(A4,5,3)=MID(A5,5,3)
How This Formula Works
- Excel looks at cell A2
- Starts at character 5
- Returns 3 characters: DAL
This is helpful when you need to group products by warehouse, city, region, or sales territory.
Common Mistakes When Using MID
One common mistake is starting at the wrong character number. Excel counts every character, including spaces, dashes, and punctuation.
Another common mistake is using the wrong number of characters. If you ask for too few characters, Excel only returns part of the value. If you ask for too many, Excel returns as many characters as it can.
MID also works best when your text follows a consistent pattern. If some codes are shorter, longer, or formatted differently, the result may not be what you expect.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> MID Function Data
