how to use excel len function to count characters from a cellExcel LEN Function can be used to count the number of characters in a cell.

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

In 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 your 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 below example, I am using 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 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 I have used LEN Function to add leading zeros in excel with the help of rept function.

And, I have also used LEN function to convert a normal text case into sentence case.

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

Sample File

What is Next?

To learn more about Excel INDEX Function you can check Microsoft’s Help Section.

There are also some functions like  MID | RIGHT | LEFT which are highly useful with len function.

Apart from this, I have a list of excel functions and some real life formulas examples.

And, if you have a unique idea to use len function, I would love to hear from you.