How to use LEN Function in Excel

The LEN function can be used to count the number of characters in a cell.

It can count text, numbers, special characters, non-printable characters, and spaces from a cell.

In the below example, we have used LEN function to count mobile number digits. As I have 10 digits in the cell, it has return 10 in the result.

get the length of mobile number using len function

Syntax

LEN(text)

  1. text A text string for which you want to count the characters.

Examples

In the below example, we have used LEN function to count letters in a cell. “Hello, World” has 10 letters with a space between and I got 11 in the result.

get the length of hello world using len function

In the below example, “22-Jan-2016” has 11 characters, but LEN function returns 5.

get length of date as per the serial number using len function

The reason behind it is that LEN function counts the characters in the value of a cell and not concerned with formatting.

Remember

  1. It will only count the characters as per the actual values in the cell. Any formatted character like “$” will not consider as a character.
  2. If you refer to an array range it will return the result in an array.
  3. If a cell is blank it will return 0 in the result.
  4. If a cell has an error value it will return that error in the result.
  5. You can also insert a text string directly into the function to count the length of the string.
  6. If you try to refer to more than one cell, It will return #VALUE!.

Sample File