Excel单元格取数公式应用20120710(国外英文资料).doc

Excel单元格取数公式应国外英文资料) First, take part of the data in the cell. Lets say that 123456 this array is in cell D2, Extract the first three functions LEFT: = LEFT (D2, 3) 2, the first three use the function RIGHT: = RIGHT (D2, 3) Extract 2-3 using the function MID: = MID (D2, 2, 2), which is from the second bit, taking 2 bits. Within the MID function mean A1 cell number starting from the second to the right to take two, because 2-3 a total of two Numbers, if you want to take 2-5, this formula should be: = MID (D2, 2, 4) If you just want to take one of them, you also use the MID, but the number is 1, for example, if you want to extract the second place: = MID (D2, 2, 1). Identify gender by identification number: = IF (IF (LEN (A2) = 15, MID (A2, 15, 1), MID (A2, 17, 1)) = 1, man, woman) 18 in A2 input 15 or id number, the above formula is the last one will be 15, 18 or at the end of the second, the remainder of a divided by 2 is equal to 1 (that is, if not divisible), it is, is the male or female. The date of birth is extracted with the identification number Enter the id number in the A3, in B3 cell you want to enter your date of birth only need to input in B3 cell: = the IF (LEN (A3) = 15, 19 MID (A3, 7, 2) in MID (A3, 9, 2) month MID (A3, 11, 2) day, the IF (LEN (A3) = 18, MID (A3, 7, 4) in MID (A3, 11, 2) month MID (A3, 13, 2) day, id card number is not correct)) When you enter your identity card number in A3 you will be prompted! Automatically determine the maximum date of each month: The maximum DATE of February 2008 is 29: = DAY (DATE (2000, 3, 1) -1), where 2008 can be defined using a cell. The 3 represents (the current month + 1) can be defined using the cell + 1. Five, the number of figures: Enter the number in A1, and you want to automatically generate the amount in capital B1, as long as you enter in B1: = the IF (ROUND (A1, 2) 0, invalid value, the IF (ROUND (A1, 2) = 0, , the IF (ROUND (A1, 2) 1, , the TEXT (INT (ROUND (A1, 2)), [dbnum2