Excel MID Function is very similar to these function but a little bit different. Excel MID Function has three parameters and all are required to retrieve the result.
How to define Excel MID function
“Excel MID Function helps us to retrieve some specific numbers of text characters from the given string in a cell.”
Excel MID Function
=MID(text, start_num, num_chars)
text- This is the text string in a cell from where we will retrieve our desired result.
[start_num] – (Required) this is the starting position of the character for our desired result.
[num_chars] – (Required) these numbers of characters we will pick from the string.
If start_num and num_chars both are zero (0), it will produce #VALUE! Error result.
If start_num is zero (0) and num_chars is one (1) or any positive integer, it will again produce #VALUE! Error result.
If start_num is one (1) or any positive integer but num_chars is zero (0), it will produce a blank result.
Blank space always counted as one character.
If srtat_num and num_chars both are negative (-) integer, it will produce a #VALUE! Error result.
If num_chars are greater than the length of string then it will retrieve whole string after start_num as a result.
If start_num is a positive integer and num_chars are negative (-) integer then it will produce #VALUE! Error result.
Here in the below example, our start_num is (1) and num_chars (5). MID function first will pick the first character from “Excelnsg” string i.e. “E” and continue pick (5) characters i.e. “Excel”.
In the below example, change start_num as (6) and num_chars (3). MID enter into cell A2 and starting pick from 6th. And continue to pick up to next 3 characters and job is finish. MID function retrieves “nsg” from “Excelnsg”.
In the below image Excel MID function has ‘start_num’ and ‘num_chars’ both are zero (0). So it will return as #VALUE! Error result.
In this example, start_num is zero (0) and num_chars is one (1). The result we get is again #VALUE! Error result because start_num should never be zero.
Here in this example, we can see that ‘start_num’ is one (1) or a positive integer. But the ‘num_chars’ is zero (0). So, in this case, we got a result as blank (“ “).
Here in the below example, both values, ‘start_num’ and ‘num_chars’ both are negative integer (-). So the result is #VALUE! Error.
If we have start_num as 2 and num_chars as 15 which is greater than the total length of the string “Excelnsg”. Then it will produce a result as “xcelnsg”, the whole length of the string.
Here in this example, start_num is a positive integer but num_chars are negative integer (-). So the result will be as #VALUE! Error.
Here, the length of the start_num is (15) which is more than total length of the string. The num_chars are (3), so it will start to find characters when the string ends. So it will provide the result as blank.