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.
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:-
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.
- 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
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:
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.
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.
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.
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.
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.
Function ignores number formatting and returns the actual digits available. Hence, the same formula returns as 3 for “$200” value.
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”.
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.
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?
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.
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.
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.
And, function returns 0 (zero) when supplied cell value is empty.
You can also use LEN function as helper function to create more complex formulas.