Excel LEN Function

Posted byPete

What does it do?

The Excel LEN function returns the number of characters contained in a single cell.  All characters are counted, this means, in addition to letters and numbers, special characters not limited to, asterisk, question marks, logical operators and also spaces, are included in the count.  Like the Excel RIGHT and Excel LEFT function, the Excel LEN function counts the characters in the cell value only, so any display values like currency codes and date formats are ignored.

Syntax Explained

Excel LEN function in the formula bar showing syntax.

Text: This is the text string where characters will be counted.

Examples

1. LEN covering many types.

Example of an Excel LEN function that counts the number of characters in the text string.

In the example above I have set up a few text strings to count characters:

  • Row 3 = Includes all characters and spaces.
  • Row 4 = As above and also the exclamation mark.
  • Row 5 = Counts the characters including the decimal point, note the cell format is set to show a “BTC” or Bitcoin sign, this is excluded as is the zero.
  • Row 6 = Counts the date value only.  All dates in Excel are made up of a 5-digit number, the dd/mm/yyyy format is a cell display only.
  • Row 7 = Counts all numbers before, after and including the decimal as per the cell value only.  The Cell display is set to hh:mm:ss format so this is ignored.
  • Row 8 = As above but I have extended the cell value display to a further decimal place.  Note how the count will ignore any zeroes after the last number in a decimal.  Both Row 7 and 8 yield the same result of 17.

Pro Tip

Combining, or using alternatives to the LEN functions such as SEARCH, FIND, MID, RIGHT and LEFT can help support text extraction techniques.  When used correctly they can make light work of something that would take hours/days using manual methods.