The excel mid function is used to extract a specified number of characters from a text string. The user is able to dictate the start and end point of the extracted characters. For example if the MID function is used for a text string: =MID(“mango”,4,2), it returns the word “Go”. This is because the first number after the text (4), specifies how many characters to the right excel should count to select the first character. This implies that in our example, this first character would be letter G. The next number in the function (2) is the number of characters you are telling excel to capture in the new text extract.
Now that we have an overview of the MID function, let’s go into details on its syntax along with some examples:
Syntax
The syntax for the MID function is pretty much straightforward:
=MID(text, start_num, num_chars)
Breaking down the arguments
text – This is the text to be extracted. You can either type it with double quotations at the beginning and end or by simply selecting a cell containing the text.
start_num – This specifies the location of the first character to extract.
num_chars – Number of characters to extract.
Usage notes
As already established, the Excel MID function is straightforward, however there are some things you must note when using it:
- The MID Function always outputs as text. Meaning that even if the text contains numbers, the output when using MID function would be text. But not a problem at all, you can combine with the VALUE function to output as a number. To learn how to use the Value function, please read this post
- A Value error occurs if the start_num is less than 1 or num_chars is less than 0
- If the start number is greater than the length of the original text string, excel returns an empty string
- If the num_chars (from the start point specified with start_num) exceed the length of text, excel returns a substring from the start_num and up to the last character of the original text.
Examples of MID Functions
The formula below extracts 4 characters starting at the 3rd character
The formula below returns 3 characters starting at the 5th character
The formula below would extract 5 characters starting at the 12th character
If the num_chars exceeds the length of the text, excel returns a substring from the start_num and up to the last character of the original text.
Other Examples
