What is the Excel LEN Function?
The excel LEN function calculates the lengths of character including spaces, numbers, and dates but does not include the number and date format.
LEN Syntax
LEN(text)
text, required, for which to calculate the length, including spaces
How to Use LEN Function in Excel
Below is an example of the LEN function and its results.
LEN Function #1
=LEN(A2)
The result is 4; there are four letters in cell A2 without spaces.
LEN Function #2
=LEN(A3)
The result is 5, increasing by 1 compared to LEN function #1. There is a space in the middle, LEN function counts all spaces wherever they are.
LEN Function #3
=LEN(A4)
The result is 4; the LEN function treats alphanumeric values just like text values.
LEN Function #4
=LEN(A5)
If LEN function calculates a number length, only counts the number and ignores the format. LEN function considers $1,234.00 as 1234. The result is 4.
LEN Function #5
=LEN(A6)
No data in cell A6? There is a space in cell A6. As mention before LEN function counts all spaces wherever the position.
LEN Function #6
=LEN(A7)
Like cell A6, no data appearance in cell A7. Unlike cell A6, which contains a space, cell A7 has an ENTER character, and LEN function counts as 1 character.
LEN Function #7
=LEN(A8)
Like cell A5, LEN function only counts the numbers, not including the format. The result is 5.
LEN Function #8
=LEN(A9)
Why are the results of date 2/1/2019 the same as number 43,497? Because Excel stores date values as numeric values. If you format the date 2/1/2019 with the number-general format, you get a number, 43497.
For a more detailed explanation of how Excel stores dates and times, read the following article:
LEN Function #9
=LEN(A10)
Why are the results different from cell A9 even though the appearance is the same 2/1/2019?
Cell A9 contains a date value, by default right aligned, while cell A10 has a text, by default left aligned. The LEN function calculates text per letter, including slash. For date value, slash mark is date format, not the data.
LEN Function #10
=LEN(A11)
Decimal separator in decimal number count as 1 character. The result is 5
LEN Function #11
=LEN(A12)
Why are the results of cell A12 and cell A11 the same, even though the appearance is very different?
Excel stores date value as integer number and time value as a decimal number. If you format 3:00:00 AM to number-general format the result is 0.125.
Read “How does Excel Store Dates and Times?” for a more detailed explanation.
