LEN function

LEN returns the number of characters in a text string, counting letters, numbers, spaces and punctuation, and returns 0 for an empty cell.

=LEN(text)

Generate a LEN formula

Describe what you need. The generator will reach for LEN where LEN is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How LEN reads its arguments
textrequiredLEN →
ArgumentRequiredDescription
textRequiredThe text argument can be a string, a cell reference, or a value that Excel or Sheets will coerce to text; if a multi-cell range is supplied in Excel it triggers an error.

Returns

LEN returns a single numeric value or an array of numbers matching the shape of the input.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Count characters in an employee's name

Employee IDNameDepartmentHire DateSalaryStatus
101Alice JohnsonMarketing2015-06-0172000Active
=LEN(B2)

Result: 13

The formula looks at cell B2, which contains the name "Alice Johnson". LEN counts every character, including the space between first and last name, giving a total of 13. This is useful when you need to enforce a maximum name length in data validation.

2. Find length of the first three letters of a department

Employee IDNameDepartmentHire DateSalaryStatus
102Bob SmithEngineering2018-09-1585000Active
=LEN(LEFT(C2,3))

Result: 3

LEFT(C2,3) extracts the first three characters of the department "Engineering", producing the string "Eng". LEN then measures that string and returns 3. This pattern is handy when you create short codes from longer department names.

3. Measure status length for every employee with an array formula

Employee IDNameDepartmentHire DateSalaryStatus
101Alice JohnsonMarketing2015-06-0172000Active
102Bob SmithEngineering2018-09-1585000Active
103Carol LeeHR2020-01-2065000On Leave
=ARRAYFORMULA(LEN(F2:F4))

Result: [6, 6, 7]

The ARRAYFORMULA wrapper tells Sheets to apply LEN to each cell in the range F2:F4. The three status strings have lengths of 6 ("Active"), 6 ("Active") and 7 ("On Leave"), so the result is a horizontal array {6,6,7}. In Excel you would use a spilled range with =LEN(F2#) after converting the range to a dynamic array.

Common errors

Which LEN error are you seeing?
LEN returned an error#VALUE!
Reference a single cell or wrap the range in a function that reduces it to a single string, such as =LEN(TEXTJOIN("",TRUE,A2:B3)).
#N/A
Wrap the call in IFNA or IFERROR, e.g., =IFNA(LEN(A2),0), to supply a fallback numeric result.
#REF!
Correct the reference by restoring the missing cell, or use INDIRECT with a valid address string.
ErrorWhy it happensHow to fix it
#VALUE!In Excel, supplying a multi-cell range like =LEN(A2:B3) forces LEN to evaluate a matrix, which it cannot handle.Reference a single cell or wrap the range in a function that reduces it to a single string, such as =LEN(TEXTJOIN("",TRUE,A2:B3)).
#N/AIf the referenced cell contains the #N/A error, LEN propagates that error because it cannot compute a length for a missing value.Wrap the call in IFNA or IFERROR, e.g., =IFNA(LEN(A2),0), to supply a fallback numeric result.
#REF!When a formula points to a cell that has been deleted or to an invalid external reference, LEN returns #REF! because the source address no longer exists.Correct the reference by restoring the missing cell, or use INDIRECT with a valid address string.

Tips and when to use something else

  • LEN counts every visible character; leading or trailing spaces are included—use TRIM first if you need to ignore them.
  • When working with numbers, LEN treats the numeric value as its text representation, so LEN(12345) returns 5.
  • If you need to count only alphabetic characters, combine LEN with SUBSTITUTE to strip out non-letters first.
  • For extracting a specific part of a string based on length, consider using LEFT, RIGHT or MID instead of LEN alone.

Related functions

LEFTLEFT returns the leftmost characters from a text string, optionally limited by a specified number of characters.RIGHTRIGHT returns the specified number of characters from the end of a text string, defaulting to one character if omitted.MIDMID extracts a specified number of characters from a text string, starting at a given position, and returns the resulting substring.

Frequently asked questions

Why does LEN return a larger number than I expect for a cell that looks empty?
Cells that appear blank may actually contain invisible characters such as spaces or non-breaking spaces. LEN counts those characters, so the result can be greater than zero. Use TRIM or CLEAN to remove hidden characters before measuring length.
Can LEN handle Unicode emojis or characters outside the basic Latin set?
Yes. LEN counts each Unicode code point as a single character, even if the visual glyph occupies more than one byte. However, some complex grapheme clusters (like flags) may be counted as multiple characters, so be aware of regional variations.
How does LEN behave in Google Sheets when I pass an entire column?
In Google Sheets, LEN is array-enabled, so =LEN(A2:A5) returns a vertical array of lengths for each cell in the range. This is different from legacy Excel where a multi-cell range triggers #VALUE!.
Is there a way to get the length of a number without converting it to text first?
LEN automatically coerces numbers to their text representation, so you can pass a numeric cell directly. If you need the count of digits without formatting (e.g., ignoring commas), wrap the number in TEXT with a plain format first, then apply LEN.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator