Excel from the rookie to become a master of the MID function

Seventh move: break off both ends (MID functions)

With the above two different strokes, this move not start from the first interception, the interception did not start from the last, but by the beginning of user-specified location and characters in length. Therefore, if the user specified starting from the first, and the Left function will be the same.

Syntax

MID (text, start_num, num_chars)

Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.

Start_num is to extract the text of the first character position. Text start_num first character is 1, and so on.

Num_chars specify that you want MID to return from the text the number of characters.

Note:

If start_num greater than the length of the text, the MID returns empty text ().

If start_num less than the text length, but more than the text start_num num_chars with the length of the MID only returns up until the end of the text characters.

If start_num less than 1, MID returns the # VALUE!.

If num_chars is negative, MID returns the # VALUE!.

If num_bytes is negative, then the MIDB return an error value # VALUE!.

Application examples:

Explained

The formula "= MID (A2, 7,8)" expressed in the A2 A2 to intercept the data contents of the cell "****** 19,851,221 ****"," 7 "that started from 7, a total of Interception of 8 characters, so the system returns the user to intercept birthday time "19851221."

OK, we have studied three strategies, but the reader may have discovered, the actual work, the raw data does not appear so neat, so we are very easy to intercept with the above three strategies. For example, the third measure example, I use the ID number is 18, but in fact, many people still use 15-bit identification number, so that, due to the length of the original data are inconsistent, resulting in the interception will cut wrong. Another example is our first example, we cut the three, but in practice, and some provinces in the name itself is 3, and therefore this situation, simply can not get the right to apply to the content.

As I mentioned in the first, in the actual work use, a single formula often is not enough, the need to use the combination of tactics. For example, identity card numbers have different lengths, we can moves, add to the median of the judgments, if the length is 18 bits, then take an 8-bit, if it is 15, then take 6. Remember that we learned both ways in front of it? But this trick very useful Oh, we often used. In addition, I will introduce the following two strokes, is used to determine the contents of the cell. One is the "blind man feeling an elephant" (Find function), allowing users to specify the contents of the cell to locate the characters to confirm their location. When the position is confirmed, the interception is an easy thing. Another trick is the "Lu Ban foot of God" (Len function), the contents of the cell allows users to measure the length, obtained its length, do the corresponding intercept processing