How to use Excel MID function (with example)

Excel MID Function

 

In this blog, we are going to learn about Excel MID Function. In earlier two blogs we have learned about Excel LEFT Function and Excel RIGHT function.

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)

Syntax

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.

Important Point

 

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.

 

Example 1

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”.

 

Excel MID Function

 

Example 2

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”.

 

Excel MID Function

 

Example 3

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.

 

Excel MID Function

 

Example 4

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.

 

Excel MID Function

 

Example 5

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 (“ “).

 

Excel MID Function

 

Example 6

Here in the below example, both values, ‘start_num’ and ‘num_chars’ both are negative integer (-). So the result is #VALUE! Error.

 

Excel MID Function

 

Example 7

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.

 

Excel MID Function

 

Example 8

Here in this example, start_num is a positive integer but num_chars are negative integer (-). So the result will be as #VALUE! Error.

 

Excel MID Function

 

Example 9

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.

 

Excel MID Function

 

 

Related Post

Excel LEFT Function

Excel RIGHT Function

 

 

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.