The LEN function in Excel counts characters in a cell, including spaces, numbers, and symbols. It’s useful for logical tests, data validation, or formatting.
What Does the LEN Function Do?
The function helps you count all characters in a cell. It includes:
- Letters
- Numbers
- Spaces
- Symbols
The LEN function only needs one argument:
- A cell reference, like
=LEN(A1).
Examples of LEN Function
| Formula | Result | Description |
|---|---|---|
=LEN("Excel 365") | 9 | Counts letters, spaces, and numbers. |
=LEN("Hello!") | 6 | Includes punctuation like !. |
=LEN(A1) | Varies | Depends on the content in cell A1. |
Why Use LEN for Logical Tests?
The function is helpful when checking data, such as validating phone numbers. For example:
- A U.S. phone number should have 14 characters, including spaces and symbols.
- You can use
=LEN(A1)to check if the length matches 14.
The LEN function will help to ckeck if the phone number pattern respect the US phone number: (3) 3-4 digits
3+3+4 = 10- But we must also include the parenthesis, the space (after the second parenthesis), and the hyphen.
- So,
1+3+1+1+3+1+4 = 14 digits
IMPORTANT REMARK: The LEN function can handle all the characters in a cell, from letters to spaces to symbols.
As you can see, most results return 14, but not all. Visualizing which result is different from 14 is not easy. This is why we must convert the formula into a test case.
Convert the formula into a logical test.
Creating a logical test is not difficult. You have to add the expected result directly to the formula.
Now, finding the wrong phone number is easy because the test result is FALSE. But we can do better.
- We can filter on column D to keep only the FALSE
- Use the IF function to Customize the result, like =IF(LEN[@Phone Number])<>14,"Wrong phone number","")
- Or highlight with a color the cases where the result is different of 14 with the conditional formatting
Related Articles
Start using the LEN function today to make your Excel tasks easier!
