The LEN function is used to determine the length of the contents of a cell or string.
Example 1
To get the length of a string you place the string inside the LEN function:
=LEN(”StringToFindGoesHere”)
This example would return 20 because there are twenty characters in the string StringToFindGoesHere.
Example 2
To get the length of the contents of a cell (A1 for instance), place the cell reference in the LEN function:
=LEN(A1)
Example 3
And finally, one of the more useful roles the LEN function can serve is validating a range that needs to be a specific length.
In this example a user needs to input 5 digit Zipcodes in range A2:A10. You will notice in the picture that every time a user inputs cell content that does not equal 5 characters the cell color changes, making it very easy to audit.
This is achieved by using the LEN function with conditional formatting, here’s how:
1. Select the Range or cell to apply Conditional Formatting to
2. Select Format on the Main Menu
3. Select Conditional Formatting
4. In the dropdown change Cell value is to Formula is
5. Enter =LEN(A2:A10)<>5
6. Click the Format… button and choose the format to apply if the condition is met. I changed the Pattern or background color
7. Click OK, OK
Here’s a snippet of my Conditional Formatting window using LEN
