LEN and FIND Functions


« Back to videos

Additional Information:

This tutorial demonstrates a practical application of the LEN and FIND functions. These functions are used to analyze text but, in themselves, can only return numerical values. Therefore they have to be used in conjunction with other functions, as in the video.

The LEN function can be used to find the length of a text string. It takes the form "=LEN("TextString")". In this case "TextString" has 10 characters, so it returns the value 10. In practice, the text will be drawn from a specific cell so, if "TextString" was typed in A1, the formula "=LEN(A1)" would also give the value 10.

The FIND function can be used to locate a certain character in a string. In its most basic form, it can be written "=FIND(WhatWeWant, TextString)". So if we have a standard US phone number such as 123-456-7890 in A1, we would expect "=FIND("-", A1)" to return a value of 4. This is because the 4th character is the "-" symbol.

The FIND function is not quite that limited in that you may be looking for a symbol that occurs more than once. You can add an extra parameter at the end to specify the character from which you wish to start searching. So "=FIND("-", A1, 5)" would return a value of 8 in the above example. This is because Excel only searches for a hyphen from the string's 5th character onwards. Note that the basic form of the FIND function is identical to its more sophisticated form, if we start our search from the first character.

If FIND fails in its search for WhatWeWant, then it will return an error "#VALUE!". So you should ensure your formulas work for all conceivable inputs.