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
Split text string to character array
Pad text to equal length
Remove characters from right
Count numbers that begin with
Remove first character
Get top level domain (TLD)
Remove protocol from URL
Abbreviate names or words
Basic outline numbering
Count total words in a cell
Get domain from email address
Cell contains number
Count cells over n characters
Find longest string with criteria
Extract word that begins with specific character
LEN function videos
How to use the REPT function to repeat things
How to check line length with conditional formatting
Excel formula error codes
How to convert Booleans to numbers
How to count characters with the LEN function
How to build all-in-one formulas
Dynamic arrays are native
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".