how to use excel len function

Quick Intro

Excel 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, I have used LEN function to count mobile number digits. As I have 10 digits in the cell, it has return 10 in the result.

Excel LEN Function to count characters from a cell

Syntax

LEN(text)

  • text A text string for which you want to count the characters. You can either refer to a cell or you can enter an absolute text in it.

More Information on Excel LEN Function

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

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 & I got 11 in the result.

Excel LEN Function to count letters in a cell

In the below example, “22-Jan-2016” has 11 characters, but LEN function returns 5. The reason behind it is that LEN function counts the characters in the value of a cell & not concerned with formatting.

Count Date Value With Excel LEN Function

LEN Function is most useful if there is a need to count characters in excel. You can check here how we have used it to add leading zeros in excel with the help of rept function. And, we also have used LEN function to convert a normal text case into sentence case.

The best part of it is that you can use it with other functions to get some dynamic results.

Sample File

download sample file to learn more about this tips

 

What’s Next?

To learn more about Excel LEN Function you can check Microsoft’s Help Section. And, if you have a unique idea to use it, I would love to hear from you.

There is also you have COUNT function which is highly useful. Apart from this, I have a list of excel functions and some real life formulas examples.