In Microsoft Excel, the MID function returns the specified number of characters from the middle of a string based on the starting position and the number of characters we specify.
For using the function, we need to understand how the MID function works in excel.
For that assume you have some sample data in column A and you can expect the result in the next column B. You need to begin with typing MID function cell P A. So type ‘eMID’ and then go to compute the function. Here you can see it takes three arguments which are text, starting number, and a number of characters. So the first argument is a string from which you want to expect the substring. The second is the starting number. It is the position of the first character from where you want the x section to begin or the location of the first character to which you want the function to be returned. And then, the number of characters you want to expect from the string. If you put your third argument more than the number of characters available, then the MID function will return all the remaining characters from the string. The MID function is also used to extract characters from the middle of a text or a string as well.
The main purpose of this function is to get or extract text from inside a string. The text is extracted as characters for the return value.
The syntax for performing the MID function is:
=MID(text, start_num, num_chars)
Here the arguments are:
text – The text or string from which we need to extract characters from
start_num – The location or starting position of the first character to be extracted from
num_chars – The number of the total characters to be extracted
By looking at some examples below, you might be able to understand the function better.
- =MID(“The cat in the hat”,5,3)
This will return “cat”
- =MID(“The cat in the hat”,16,3)
This will return “hat”
This will return “ple”
This will return “348” as text
Some of the other MID formula examples that can be obtained include the functionalities of translating numbers to text, stripping numeric characters from cells, highlighting numbers that include symbols, abbreviating names or words, LAMBDA strip characters, splitting dimensions into three parts, parsing time strings to time, data validating specific characters only, etc.
The MID function is used as a Worksheet function (WS), as well as a VBA function (VBA). The above examples are of MID’s Worksheet functions.
As a MID’s VBA function example:
- Dim LResult As String
This will return “ab”
The MID function applies to the following models or versions of Excel:
Excel 2000, Excel XP, Excel 2003, Excel 2007, Excel 2010, Excel 2011 for Mac, Excel 2013, Excel 2016, Excel 2019, and Excel for Office 365.