Count the number of characters in a string
LEN(text)
Count up the number of characters in a text string, number or date using either defined values (e.g. "string") or cell references (e.g. C3).
LEN syntax requires just the defined value or cell reference to be counted.
LEN with a text string.
In the example below, the LEN function is used to count the number of characters in the string "North America", both by entering the defined value "North America" into the function and by using a cell reference.
Use the LEN function to count the number of characters in a text string.
LEN with a number.
In the example below, the LEN function is used to count the number of characters in the GDP and GDP per capita numbers. Note how decimal points are included in the count but number formatting (e.g. $) is excluded.
Use the LEN function to count the number of characters in a number.
LEN with a date.
In the example below, the LEN function is used to count the number of characters in a date. Note how date formatting is ignored; instead the count represents the underlying number (44528).
Use the LEN function to count the number of characters in a date.
Syntax
=LEN(text)
The LEN function consists of 1 argument:
- text. Required. The text from which to count the characters.
Try it now!
- Enter your formulas into the grey cells.
Scope
LEN can be applied to text, numbers and dates using either defined values or cell references.
|
=LEN("Sub-Saharan Africa") || result is 18 counting all characters and spaces |
|
=LEN(1000) || result is 4 |
|
=LEN(A1) where cell A1 holds the date 1/1/2020 || result is 5. 1/1/2020 is the number 43831. |
|
=LEN(B2) where cell B2 holds the value 1.23 || result is 4 including the numerals and the decimal point. |
Caveats
- Number formatting is not included in the character count but negative symbols are.
- e.g. $41,904.85. || result is 8 as the $ character is excluded but the decimal point is included.
- Trailing spaces are included in the character count
- e.g. Canada . || result is 7 as the trailing space after Canada is included.
- Blank cells have a character count of zero.
Related Functions
- LENB() counts the number of bytes used to represent characters in a text string.
- LENB() counts 2 bytes per character for a DBCS language (Japanese, Chinese (Simplified), Chinese (Traditional) and Korean).
- For other languages, LENB() behaves the same as LEN(), counting 1 byte per character.
Functions Category