Excel Function: LEN
Purpose
The LEN function counts and returns the number of characters in a text value. Its output is always a value of type number.
Invocations of the LEN function take the form:
LEN(text)
Arguments
| Argument | Argument type | Description |
|---|
| text | Mandatory | A text value whose characters will be counted. |
Related Functions
Error Conditions
| Error value | Condition type | Condition |
|---|
| #VALUE! | Bad argument | A value of type currency, geography, or stock was supplied to the text argument. |
Examples
| Example | Formula | Result | Description |
|---|
| 1 | =LEN("") | 0 | Determine the number of characters in an empty text value.- Since an empty text value contains no characters, the result is 0.
|
| 2 | =LEN("Lorem ipsum") | 11 | Determine the number of characters in a text value made up of ASCII characters.- In this example, each word consists of 5 characters.
- The space character between the two words is 1 character.
- 5+5+1=11
|
| 3 | =LEN("漢€") | 2 | Determine the number of characters in a text value made up of non-ASCII characters.- Each non-ASCII character contributes 1 to the length.
- In this example, the length of the text value is two.
|
| 4 | =LEN(100.99) | 6 | Apply the LEN function to a numeric value.- The number 100.99 is converted to the text value “100.99”.
- The text value “100.99” consists of 6 characters.
|
| 5 | =LEN(-100.99) | 7 | Apply the LEN function to a negative numeric value.- The number -100.99 is converted to the text value “-100.99”.
- The text value “-100.99” consists of 7 characters.
|
| 6 | =LEN(DATE(2023,4,8)) | 5 | Apply the LEN function to a date value.- Excel models all dates as numbers.
- A number represents how many days have passed between it and 1 January 1900.
- 8 April 2023 is converted into the 5-character-long text value “45024”.
- The LEN function returns the number 5.
|
| 7 | =LEN(TIME(18,0,0)) | 4 | Apply the LEN function to a time value.- Excel models all times as numbers.
- A number represents the fraction of a day that has passed when a time is reached.
- The time 18:00:00 is converted into the 4-character-long text value “0.75”.
- The LEN function returns the number 4.
|
Examples: Errors
| Example | Value supplied to “text” argument | Result | Description |
|---|
| 1 | London | #VALUE! | Apply the LEN function to a stock value.- The stock value cannot be treated as a text value by the LEN function.
- Because of this, the #VALUE! error value is returned.
|
| 2 | USD/GBP | #VALUE! | Apply the LEN function to a currency value.- The currency value cannot be treated as a text value by the LEN function.
- Because of this, the #VALUE! error value is returned.
|
| 3 | MICROSOFT CORPORATION (XNAS:MSFT) | #VALUE! | Apply the LEN function to a geography value.- The geography value cannot be treated as a text value by the LEN function.
- Because of this, the #VALUE! error value is returned.
|
These errors can be mitigated by converting values of type stock, currency and geography to text before passing them to the LEN function. I.e.:
=LEN(VALUETOTEXT(text))