Excel mid function

The mid function specified the extraction of elements from the middle of the string. The element can be in the form of numbers, characters, or symbols. It also includes spaces between two or more words or numbers. We can specify the number of characters we want to extract from the middle of the given data element.

It is given by:

Where,

Text refers to the specified cell from which we want to extract the elements.

Start_position refers to the position from where we want to start extracting.

Number_of_characters refers to the number of characters we want to extract from the given data in excel.

For example,

Data string = 123ABC456

Here, we want to extract the code in the form of characters present in the middle of each string. So, we will use the MID() function.

It is given by:

MID(123ABC456, 4, 3)

Here, the character is present at the fourth position (A). Excel will start extracting three characters from the fourth position. The extraction will occur at the respective positions 4^th, 5^th, and 6^th. Thus, excel will return the output as:

Output: ABC

Here, we will also discuss the MIDB() function, another part of the MID() function that counts double-byte character as 2. The MID() function counts the double-type character as 1. It will be discussed later in the topic.

Important points

• If the start_position in the function is less than 1 (for example 0), excel will return ‘#VALUE!’
  For example,
  =MID(G13,0,7)
  Here, excel will return ‘#VALUE!’
• If the number_of_characters specified are greater than the length of the text, excel will consider extra spaces as characters in the front of the text. It means that excel will return characters upto the end of the text.
  For example,
  Text: ABCD1234AB
  =MID(ABCD1234AB,2,25)
  Here, excel will return BCD1234AB.
• If the start_position is greater than the length of the text, excel will return the blank space “”.
  Text: ABCD1234AB
  =MID(ABCD1234AB,12,2)
  Here, excel will return “”.
• If the number_of_characters are negative, excel will return ‘#VALUE!’
  For example,
  =MID(G13,1,-7)
  Here, excel will return ‘#VALUE!’

Let’s first consider some examples of the excel MID() function.

Examples

Example 1: To extract the code number present in the string.

Here, each string in excel consists of a code. We want to extract the number code from the given string. The number code is present in the middle o f the string.

It is just an example for better understanding. We can also create our own example.

Consider the below steps:

1. Click on the first cell of the column Code Number.
2. Type ‘=MID(A3,4,3),‘ as shown below:
   
3. Press Enter. The desired number code from the middle of the given string will appear. The extraction will start from the fourth character of the string and end at the sixth character (4^th, 5^th, and 6^th). It is because we have specified number_of_characters as three. It also counts the spaces in between if any.
4. Click on the bottom-right corner of the cells, and drag it to the last cell of the column. The MID() formula will be automatically applied to the rest of the cells of the column, as shown below:

The MID() function on each cell will work as:

=MID(ABC123DEF,4,3)  It will return '123' as the output  =MID(IJK234LMB,4,3)  It will return '234' as the output  =MID(HTY654IYT,4,3)  It will return '654' as the output  =MID(TYR765HYU,4,3)  It will return '765' as the output  =MID(UTE678FVG,4,3)  It will return '678' as the output  =MID(FKH194GTY,4,3)  It will return '194' as the output  

Example 2: To extract a word from the given sentences.

Consider the below steps:

1. Click on the first cell of the column Extracted word.
2. Type ‘=MID(A3,12,3),‘ as shown below:
   
3. Press Enter. The desired number code from the sentence will appear. The extraction will start from the fourth character of the string and end at the twelfth character (12^th, 13^th, and 14^th). It is because we have specified number_of_characters as three.
4. Click on the bottom-right corner of the cells, and drag it to the last cell of the column. The MID() formula will be automatically applied to the rest of the cells of the column, as shown below:

Note: Like the MID() function, LEFT() and RIGHT() functions are also used to extract the number of characters from the given string. The LEFT() function extracts the characters from the left side of the specified string and RIGHT() function extracts the characters starting from the right side of the specified string.

Example 3: To extract from the list of five students.

Here, we will first extract the first name from the list of students. The list is given below:

The given list has specified first names of upto five characters. The first name has four characters, and the fifth one is extra space. Excel will consider the extra space as the fifth character.

Consider the below steps:

1. Click on the first cell of the column First Name.
2. Type ‘=MID(A3,1,5),‘ as shown below:
   
3. Press Enter. The desired number code from the sentence will appear. The extraction will start from the first character of the string and end at the fifth character (1^st, 2^nd, 3^rd, 4^th, and 5^th). It is because we have specified number_of_characters as five.
4. Click on the bottom-right corner of the cells, and drag it to the last cell of the column. The MID() formula will be automatically applied to the rest of the cells of the column, as shown below:

MIDB() function in excel

Let’s have a quick look at the MIDB() function in excel to eliminate the confusion between the MID() function and MIDB() function.

MIDB() function considers the double-byte character as 2 when we enable editing a language that supports DBCS (Double-Byte Character Set) and set it as a default language. Otherwise, it considers 1. In the case of MID() function, excel considers both the double byte character and single byte character as 1.

The double-byte languages are Chinese, Korean, and Japanese. Such languages are difficult to represent in a single quote.

It is given by:

Where,

Text refers to the specified cell from which we want to extract the elements.

Start_position refers to the position from where we want to start extracting.

Number_of_bytes refers to the number of characters we want to extract from the given data in excel.

The output and the conditions of the MIDB() are similar to that of MID() function.

Let’s consider some examples.

Example 1:

Text: Ahaan Malhotra

Syntax: =MIDB(Ahaan Malhotra,1,5)

Value returned: Ahaan

Example 2:

Text: IJK234LMB

Syntax: =MIDB(IJK234LMB,4,3)

Value returned: 234