From rookie to become a master of Excel Len function

Ninth trick: Luban ruler of God (Len function)

This trick is used on the length of the cell contents were measured, drawn its length, do the appropriate treatment.

Syntax

LEN (text)

Text is to find the length of the text. Spaces count as characters.

Application examples:

Explained

The formula "= Len (A2)" in A2 that data to find the length of the contents of the cell A2 "****** 19,851,211 ****", system measurement, return the length of "18."

Well, we have finished school, "blind man feeling an elephant" and "Lu Ban foot of God", along with three strategies learned in the last (left head to tail-Left function to the end of the first left-Right functions, and break off both ends -Mid function), the treatment of some text, we will be able to play with hands like top.

Below we give an example, do a real operation. If you have a bunch of staff identity card number registration form, but no date of birth of employees in this column alone, but had the staff really need your birthday information. One by one to copy it? ? This time too it! Do not worry, using the above recipe, and within a minute you can be "production" out.

First idea is to use "break off both ends" function, before and after the contents of the identity card number removed, leaving the middle of the date of birth. ID number but because there are two lengths (15 and 18), directly applied, it will certainly cut wrong. So we have to add "Lu Ban foot of God", first measure out the length, coupled with the "best of both worlds," this mode, the length of the different numbers, we do different interception, the problem is solved.

The figure above, two colors of data length is inconsistent, but the use of formula, we are simply cut out the birthday data. The formula "= IF (LEN (A2) = 15,19 & MID (A2, 7,6), MID (A2, 7,8))", we use the IF function, with the Len function to determine the length of the A2, and if equal 15, returns "19 & MID (A2, 7,6)", said that if 15-bit ID number, just before the make up of its "19" (Note: "&" symbol in Excel, for the two data combined), then the data in cell A2 from 7 start, cut six out together just eight. If not 15, then return "MID (A2, 7,8))", said the A2 cell directly in the data, starting from 7, 8-bit out of the interception. Done after the first formula, whether there are thousands or tens of thousands of the following data can be delayed in the end.

For relatively simple requirements of the user, get this result has been good enough. But in fact, taken out of this data, not the date format. Therefore, we can not treat the date as to deal with it, such as changing the date format, or set conditional formatting so that the day of the birthday of the data is displayed as red.

To make the changes to date, it is actually very simple. Only one trick we have not introduced-Datevalue, at the same time, remove the data, add additional separators to allow system identification. I first formula listed here, interested users can try.

= DATEVALUE (IF (LEN (A2) = 15,19 & MID (A2, 7,2) & - & MID (A2, 9,2) & - & MID (A2, 11,2), MID (A2, 7,4) & - & MID (A2, 11,2) & - & MID (A2, 13,2)))

Remember: the result of the formula is a time serial number, date format, you can set your own. The figure, C3 and C5 cell "30720,30034," is because they can not set the date format displayed directly sequence value.

Here we look at a use of the "blind men feeling like" function example.

If there is such a string of data, format similar to Bill Gates (****) Bill.Gates @ hotmail.com or the Charles Peng (****) Charles.Peng @ sqtong.com, we need to remove part of one of the e-mail address. E-mail address because of its long-short, therefore, can not directly capture, Len function alone can not be achieved. But the investigation found that under the concept, e-mail address begins ")" after, so we can use the "blind men feeling like" Find function, first set in each data ")" position, then measure out the entire data length Len, After the subtraction, that is, the length of e-mail address, so use the "left tail to head" function will need the data can be removed. Formula is: = RIGHT (A2, LEN (A2)-FIND (), A2)).