LEN Function in Excel

LEN Function Cover Image

The Excel LEN is an in-built function hence frequently used in day to day life. The function is really simple and easy to use. It belongs to Text family which has many other useful functions like; FIND, SEARCH, MID, MAX, LEFT, RIGHT etc.

What it does?

The LEN function is used to count the length of the specified string, in other words it counts the number of characters in a string and return.

Uses:-

The LEN function can be used at many places in different situations. The function becomes more powerful when used with other functions. For example, using with RIGHT function, last name or specific string can be extracted.

Syntax & Arguments:-

=LEN(text)

LEN Function Syntax & Argument

text – The only argument which is required. It helps you to measure the length of the text string. This can be supplied directly to the function, cell reference containing string or formulas returning text string.

Additional Notes:-

  • Text, number, date, time, special characters, space, and non-printable characters are counted
    • Dates & Times are stored as serial numbers, and the function counts the actual serial number and returns
  • Leading or trailing spaces are also counted
    • Use Trim function to remove extra spaces from string
  • Function ignores any type of number formatting while counting length of string
  • Function does not count errors therefore returns the same error as result
  • Returns 0 if cell value supplied in argument is empty

Examples:-

In the following example, I have taken different type of inputs to supply as the argument into the function to count the number of characters.

Let’s take a look at each example:

LEN Function Examples

In the first example, when I supplied cell A2 which contains text string “Hello”, function returned 5 as total character length, since there are 5 letters in the text string.

LEN Function Example 1st

And, when I copy the same formula down to B3, argument now refers to cell A3 that has “Microsoft Excel” text string, and the formula this time returns to 15, that is because the space is also included and counted as character.

LEN Function Example 2nd

Similarly when there are leading or trailing spaces in the string that also gets counted. The same formula returns as 22 number of the string “   Microsoft   Excel  “ which is located in cell A4.

LEN Function Example 3rd

The spaces aren’t visible but still counted, if you see such strings with too many or leading/trailing spaces, you can use TRIM function to remove them.

LEN Function Example 3rd-A

Now take a look at how function treats when text string is supplied in the argument. The LEN function returns as number 4 for the number “1000” that is because the numbers are considered as a string. Either it is supplied directly to the argument or as a reference to the cell, function will count and return the number of digits.

LEN Function Example 4th

Function ignores number formatting and returns the actual digits available. Hence, the same formula returns as 3 for “$200” value.

LEN Function Example 5th

This is because the content may appears in currency format because of formatting however the cell actually stores 3 digit.

But when the cell contain numbers with decimal values, function counts till last decimal digit including decimal separator and returns 7 for “$1,000.95”.

LEN Function Example 6th

When number 10,000,000 is formatted as number format with thousands separator supplied in the argument to the function, then the function counts 8 digits and returns.

As mentioned above, function ignores any type of number formatting and only counts the raw value stored in cell.

LEN Function Example 7th

Then, what about Date value? When the same formula is copied down to cell B9, it gives 5 as date value, however; if you look at cell reference it has a normal and regular date, so shouldn’t the formula return 8 instead of returning number 5?

LEN Function Example 8th

That is because, dates are stored as integer value in Excel and when you change the date format into general format, you’d see 5 digits number representing the date, for example; 42370 stored as January 01, 2016.

LEN Function Example 8th-A

Similar, is the case with Time 9:36 PM for which function returns as 3 which is because when you convert 9:36 PM into general format, it would return to 0.9, which has only 3 characters including decimal separator.

LEN Function Example 9th

And, for #DIV/0! error, we get same #DIV/0! as error, so whenever any error is supplied to the argument into the function, the same error returns the formula as answer. Let’s say if we supply “#VALUE!” we’ll get “#VALUE!” in result.

LEN Function Example 10th

And, function returns 0 (zero) when supplied cell value is empty.

LEN function GIF File of all Examples

You can also use LEN function as helper function to create more complex formulas.

Leave a Reply