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
Get domain from email address
Abbreviate names or words
Count specific words in a cell
Count unique values with criteria
Count total characters in a cell
Get last name from name
Count numbers that begin with
Strip non-numeric characters
Highlight cells that end with
Remove last word
Get page from URL
Basic outline numbering
LAMBDA replace characters recursive
Extract nth word from text string
Count specific characters in a range
LEN function videos
Excel formula error codes
How to build all-in-one formulas
How to convert booleans to numbers
Dynamic arrays are native
How to use the REPT function to repeat things
How to check line length with conditional formatting
How to count characters with the LEN function
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".