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.
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:
- Text is a required argument, this is the text string containing the characters you want to retrieve.
- 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.
- 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
- If num_chars <0 ( num_chars is a negative number), the MID function returns the #VALUE! Error value.
- If start_num <1 , the MID function returns the @VALUE! Error value.
- If start_num is greater than the text length, the MID function returns blank text ('').
- 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)
Or you can refer to the cell containing the string to extract.
Example 2: Combine MID function with VLOOKUP, LEFT and IF functions to handle the following data table.
How to handle unit price:
- 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:
- LEFT (B6; 2) returns the first 2 characters in the order code, these 2 characters are the strings to search.
- A16: D19 is the detection table, which is the wholesale / retail price list.
- 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.
- 0 is the correct detection type.
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)
You just need to copy the formula.
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.
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!
You should read it
- INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT function
- VLOOKUP function to use and specific examples
- Offset function in Excel - Usage and examples
- IRR function in Excel - Usage and examples
- DSUM function in Excel, how to use DSUM function and examples
- Function Address - The function returns the address of a cell in Excel (usage, examples, examples)
- How to use the SUMIF function in Excel
- Excel date function - Usage and examples
May be interested
- DCount function in Excel - How to use the DCount function and examples using the DCount functiondcount function in excel - how to use the dcount function and examples using the dcount function you have a large list of students who want to get a high total score, but don't want to spend a lot of time. in this article, introduce to you the dcount function in excel. help c
- Functions rounded up, how to round up in Excelfunctions rounded up, how to round up in excel. you are looking for rounding up functions to round numbers up in excel. so, please refer to the following article to know how to round up using functions in excel.
- INDIRECT function in Excel - How to use INDIRECT function and examples using INDIRECT functionindirect function in excel - how to use indirect function and examples using indirect function. you want to refer to a range of data without changing the formula in the cell. the following article introduces you to the indirect function in excel to help you reference
- Random function in Excel (RAND function), how to use the Random function and examplesrandom function in excel (rand function), how to use the random function and examples. you need to get a random value in a range of values. to avoid duplicate values and objectivity, you should use the rand () function. the article below gi
- Comparison functions in Excel - How to use comparison functions and examples using comparison functionscomparison functions in excel - how to use comparison functions and examples using comparison functions with a large amount of data, you want to check for duplicates by checking normally, it is really hard. in this article, introduce to you the functions
- How to align in Word - Instructions on how to align in Word 2007, 2010, 2013, 2016how to align in word - instructions on how to align in word 2007, 2010, 2013, 2016. for each document there are general provisions on how and how to present text. the ministry of the interior has issued a circular on guidelines on techniques and techniques for presenting administrative documents no.