Purpose
Return value
Syntax
=LEN(text)
- text - The text for which to calculate length.
How to use
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.
LEN function examples
Abbreviate names or words
Find longest string
Extract last two words from cell
Count unique values with criteria
Count total characters in a range
Remove first character
Find longest string with criteria
Count total words in a range
Count specific words in a cell
Extract nth word from text string
Count line breaks in cell
Highlight blank cells
Cell contains number
Capitalize first letter in a text string
Split text string to character array
LEN function videos
Dynamic arrays are native
How to count characters with the LEN function
Excel formula error codes
How to convert booleans to numbers
How to build all-in-one formulas
How to use the REPT function to repeat things
How to check line length with conditional formatting
Related functions
RIGHT Function
The Excel RIGHT function extracts a given number of characters from the right side of a supplied text string. For example, =RIGHT("apple",3) returns "ple".
LEFT Function
The Excel LEFT function extracts a given number of characters from the left side of a supplied text string. For example, =LEFT("apple",3) returns "app".
MID Function
The Excel MID function extracts a given number of characters from the middle of a supplied text string based on the provided starting location. For example, =MID("apple",2,3) returns "ppl".