Description
The Excel MID function returns a certain number of characters from the middle of the specified text string.
As a quick example, =MID(“worksheets”,5,6) returns “sheets.”
In plain English, you tell Excel to pick a text string (the first argument; “worksheets”) and extract a certain number of characters from it by specifying the location of the first character from where to start the extraction (the second argument; “5” or the first letter “s” in the word “worksheets”) and the number of characters to want to extract (the third argument; “6” or “sheets”).
Purpose
The function helps extract the characters from a string which would be problematic to pull using the Excel LEFT function or RIGHT function.
When combined with other functions like SEARCH and LEN, you can slice and dice any data with ease, be it names, dates, or CSV files.
Syntax
=MID(text,start_num,num_chars)
Arguments
- text – the location of the value you want to extract from.
- start_num – the position where the selection starts.
- num_chars – the number of characters that you want to extract from the string.
Return Value
The MID function returns the extracted characters as string/text values.
Usage Notes
1. Applying the MID function to numeric values converts them into text/string values.
2. If the num_chars argument is greater than the overall number of characters in the text string, Excel returns all of the remaining characters in the string starting from the location specified by the start_num argument.
3. If start_num is less than “1,” the #VALUE! error is triggered.
4. When the start_num argument is greater than the overall number of characters in your text string, the MID function returns an empty string.
5. If num_chars equals zero(“0”), the MID function returns an empty string.
6. Each space is counted as one character.
Examples
For you to better understand how the formula works, let’s pick one phrase “James Bond 007” and run with it to show you different ways to use the Excel MID function.
This MID function returns 4 characters starting at the 7th character:
=MID("James Bond 007",7,4) // Output: BondThis MID function returns 3 characters starting at the 12th character:
=MID("James Bond 007",12,4) // Output: 007This function extracts everything starting at the 7th character since the num_charts argument is greater than the remaining number of characters in our string.
=MID("James Bond 007",7,100) // Output: Bond 007How to Use the MID Function in Excel VBA
Thought we were done? Well, not so fast.
The Excel MID function can also be used in VBA, providing you with more flexibility with your data. Here’s a quick example showing you how to use the MID function in Excel VBA.
Sub VBA_MID_Function()
MidValue = Mid(Range("A2"), 7, 4)
Range("B2") = MidValue
End Sub
If you’re unfamiliar with VBA, here’s how you can adjust.
First, leave the first and last lines of code unchanged. These are merely containers holding the VBA code together.
The second line of the VBA code declares a new variable (MidValue) storing the extracted characters using the MID function.
This VBA MID function selects cell A2 and extracts 4 characters starting at the 7th character, returning the word “Bond.”The third line is responsible for moving those extracted characters from the VBA editor into cell B2.
