Purpose

Return value

Syntax

=LEN(text)
  • text - The text for which to calculate length.

Using the LEN function

The LEN function returns the number of characters in a given text string. LEN takes just one argument, text . LEN counts the number of characters in text , including space and punctuation, and returns a number as the result. If text is an empty string ("") or text is a reference to an empty cell, LEN returns zero. LEN will also count characters in numbers, but number formatting is not included.

Examples

LEN returns the count of characters in a text string:

=LEN("apple") // returns 5

Space characters are included in the count:

=LEN("apple ") // returns 6

LEN also works with numeric values, but number formatting is not included:

=LEN(1000) // returns 4
=LEN($1,000) // returns 4

The LEN function often appears in other formulas that manipulate text in some way. For example, it can be used with the RIGHT and FIND functions to extract text to the right of a given character:

=RIGHT(A1,LEN(A1)-FIND(char,A1)) // get text to right of char

FIND returns the position of the character, which is subtracted from length, calculated with LEN. RIGHT returns the text to the right of that position. Full explanation here .

Notes

  • LEN returns the length of text as a number.
  • LEN works with numbers, but number formatting is not included.
  • LEN returns zero if a value is empty.

Technical Notes

For some characters, the LEN function returns the number of UTF-16 code units used to encode the string rather than the number of user-perceived characters. For example, given the character πŸ™‚ (U+1F642) as input, the LEN function returns two.

=LEN("πŸ™‚") // returns 2

Characters in the Basic Multilingual Plane (U+0000 to U+FFFF), like basic letters (A-Z) and symbols like Ο€ (U+03C0) and βœ“ (U+2713), are encoded with one code unit. For these characters, the LEN function returns a length of one.

=LEN("Ο€") // returns 1

Supplementary characters beyond the BMP (Basic Multilingual Plane), such as emoji, require 2 code units in UTF-16. For these characters, the LEN function returns a length of two. The behavior of the LEN function is different from the LEFT and RIGHT functions, which expect the number of characters ( code points ) as input as opposed to code units , which LEN returns.

=LEFT("πŸ˜ŠπŸ˜πŸ™",1) // returns 😊
=RIGHT("πŸ˜ŠπŸ˜πŸ™",2) // returns πŸ˜πŸ™

Purpose

Return value

Syntax

=LOWER(text)
  • text - The text that should be converted to lower case.

Using the LOWER function

The LOWER function converts a text string to all lowercase letters. The LOWER function takes just one argument, text , which can be a text value or cell reference. LOWER changes any uppercase characters in the text value to lowercase. Numbers, punctuation, and spaces are not affected. LOWER will convert numbers to text with number formatting removed.

Examples

=LOWER("Apple") // returns "apple"
=LOWER("APPLE") // returns "apple"

Numbers or punctuation characters inside a text string are unaffected:

=LOWER("XYY-020-KWP") // returns "xyy-020-kwp"

If a numeric value is given to LOWER, number formatting is removed. For example, if cell A1 contains the date June 26, 2021, date formatting will be lost and LOWER will return a date serial number as text:

=LOWER(A1) // returnsΒ "44373"

If necessary, you can use the TEXT function to work around this limitation. Use TEXT to convert the number to a text value, then pass that value into lower:

=LOWER(TEXT(A1,"mmmm d, yyyy")) // returnsΒ "June 26, 2021"

Use the LOWER function to convert text to lowercase, use the UPPER function to convert text to uppercase, and use the PROPER function to capitalize the words in a text string.

Notes

  • All letters in text are converted to lowercase.
  • Numbers and punctuation characters are not affected.
  • Number formatting is removed from standalone numeric values.