The MID function in Excel, how to use the MID function, and examples

The MID function in Excel is a function of the Text function group, you use the MID function if you want to cut the string in the middle of the text string when processing the string. If you do not know or do not understand the MID function, you can refer to the article on how to use the MID function with the example illustrated below.

The MID function in Excel, how to use the MID function, and examples Picture 1

Here the article shares to you the description, syntax and examples of using the MID function, invite you to follow along.

MID function description

The MID function is a function that cuts the string in the middle, returning a specific number of characters from a text string, and the position to cut the string will be specified by you.

The MID function always counts each character as 1, whether it's a single byte or a double byte, regardless of the default language setting?

MID function syntax

= MID (text; start_num; num_chars)

Inside:

1. Text is a required argument, this is the text string containing the characters you want to retrieve.
2. start_num is a required argument, this is the position of the first character you want the MID function to take in the text string.
3. num_chars is a required argument, this is the number of characters you want the MID function to return starting at the position start_num.

Note

1. If num_chars <0 ( num_chars is a negative number), the MID function returns the #VALUE! Error value.
2. If start_num <1 , the MID function returns the @VALUE! Error value.
3. If start_num is greater than the text length, the MID function returns blank text ('').
4. If start_num is smaller than the text length, but start_num plus num_chars is greater than the text string length, the MID function returns from start_num to the last character of the text.

MID function example

Example 1: Use the MID function to extract 3 characters in the string 'MID function in Excel' starting at position 5.

You can enter the string directly into the MID function, where start_num is 5 and the number of characters cutting num_chars is 3.

= MID ("MID function in Excel"; 5; 3)

The MID function in Excel, how to use the MID function, and examples Picture 2

Or you can refer to the cell containing the string to extract.

The MID function in Excel, how to use the MID function, and examples Picture 3

Example 2: Combine MID function with VLOOKUP, LEFT and IF functions to handle the following data table.

The MID function in Excel, how to use the MID function, and examples Picture 4

How to handle unit price:

1. Using VLOOKUP to search for the 2 characters to the left of the order code (using LEFT) to look up in the Retail / Wholesale Price Table, the return value of the VLOOKUP function you use the IF function if the 4th character in the code If the order is S, then the corresponding value in column 3 of the Price List will be returned. If L (other than S) is returned, the corresponding value in the fourth column of the Price Table will be returned. In terms of the IF function you need to split the fourth character of the order code is S or L.

If you do not understand the VLOOKUP function, you can see more about using the VLOOKUP function here http://TipsMake.vn/ham-vlookup-in-excel/

Specifically, you enter the following function formula in cell E6

= VLOOKUP (LEFT (B6; 2); A16: D19; IF (MID (B6; 4; 1) = "S"; 3; 4); 0)

Inside:

1. LEFT (B6; 2) returns the first 2 characters in the order code, these 2 characters are the strings to search.
2. A16: D19 is the detection table, which is the wholesale / retail price list.
3. IF (MID (B6; 4; 1) = "S"; 3; 4) This IF function will return a value of 3 or 4, which is the position of the column that returns the corresponding data in the lookup table.
4. 0 is the correct detection type.

The MID function in Excel, how to use the MID function, and examples Picture 5

Next, if you want to copy the Vlookup function formula with LEFT, IF, MID, then when you enter the Vlookup formula to the wholesale / retail price list A16: A19, press the F4 key to fix this table space. Now the Vlookup function will look like this:

= VLOOKUP (LEFT (B6; 2); $ A $ 16: $ D $ 19; IF (MID (B6; 4; 1) = "S"; 3; 4); 0)

The MID function in Excel, how to use the MID function, and examples Picture 6

You just need to copy the formula.

The MID function in Excel, how to use the MID function, and examples Picture 7

To calculate the Thanh Tien column, you only need to use the * operator to multiply the Sales SL by the Found Unit Price and copy the formula down to the cells below. So your data sheet has been processed.

The MID function in Excel, how to use the MID function, and examples Picture 8

Thus the article has shared syntax, for example, how to use the MID function in Excel. Hopefully through this article, you will better understand the MID function. You can flexibly combine the MID function with other functions so you can get the most out of it. Good luck!