LEN Function in Excel: Count Characters in Cells

Formulas & functions · Updated November 20, 2026

In Excel, use the LEN function to count the characters in a cell: with text in A2, enter =LEN(A2). LEN counts letters, numbers, punctuation, spaces, and other characters in the cell’s text, making it useful for checking text length and spotting unexpected differences.

What the LEN function counts

LEN returns a number representing the length of a text string. It counts spaces as well as visible characters, so a cell containing “blue sky” has eight characters: four letters, one space, and three letters. Punctuation and digits count too. For example, a product code’s hyphen contributes one character to its length.

The function works on text typed into a cell and on text returned by another formula. It also accepts numbers and converts them to text for the count. A number such as 1234 therefore has a length of four, regardless of how the cell is formatted to display it. Formatting a number as currency or adding decimal places does not add those displayed symbols to the stored value that LEN counts.

A genuinely empty cell returns 0 when referenced by LEN. A formula that returns an empty string also has a length of zero, though the cell contains a formula. That distinction matters when you are checking whether a worksheet cell is truly empty rather than merely displaying nothing.

Syntax and a first example

LEN takes one required argument: the text whose length you want. That argument can be a cell reference or a text value. For a worksheet example, assume A2 contains the text Cedar. Enter the following formula in another cell:

With Cedar in A2, =LEN(A2) returns 5.

You can copy the formula down a column to calculate the length of each row’s text. In Microsoft 365 for Windows, select the formula cell and drag its fill handle down, or copy and paste it into the cells below. Excel adjusts the relative reference from A2 to A3, A4, and so on. To keep the source fixed when copying, use an absolute reference such as $A$2; that is helpful when every formula should check the same cell.

LEN returns a count, not the text itself. The result is a number that you can use in comparisons, conditional formatting rules, or other formulas. It does not remove spaces, validate a code against a business rule, or tell you which character is at a particular position.

Use LEN to check text length

A common use is finding entries that do not meet a required length. Suppose an imported list of product IDs should contain exactly seven characters, and the IDs are in A2:A100. First calculate each ID’s length in a neighboring column, then filter that column for values other than 7. This makes exceptions easy to inspect without changing the original data.

For a single-cell check, assume the ID is in A2. The following formula returns TRUE when its length is seven and FALSE otherwise:

With the ID in A2, =LEN(A2)=7 returns TRUE for AB-1047, which has seven characters.

This check tests length only. It does not establish that the ID has the right pattern: a seven-character value containing letters in the wrong positions still passes. For stronger validation, combine a length check with rules for the allowed characters or use Excel’s data validation features when users enter new values. Always consider whether leading zeros are meaningful. If an identifier such as 00421 is treated as a number, Excel may remove the leading zeros before LEN can count them. Store identifiers as text when the zeros are part of the ID.

Length checks are also useful for comparing imported fields. If a postal code, department code, or reference number is expected to have a fixed number of characters, LEN can quickly flag rows to review. It is a screening tool, not proof that every value is correct.

Spaces and hidden characters

Because LEN counts spaces, a value that looks right on screen may have a different length than expected. A trailing space after a name adds one to the count; two leading spaces add two. These extra spaces can cause exact lookups and text comparisons to fail even when the visible words appear identical.

Assume A2 contains two spaces, Cedar, and then two more spaces. Its total length is nine: two leading spaces, five letters, and two trailing spaces. To compare the original length with the length after Excel’s TRIM function removes leading and trailing spaces and reduces repeated ordinary spaces between words, use this check:

With the padded text in A2, =LEN(A2)-LEN(TRIM(A2)) returns 4 for “ Cedar ”.

That difference is not limited to leading and trailing spaces. TRIM also changes multiple ordinary spaces between words to a single space, so the difference includes those extra internal spaces too. TRIM is useful for cleaning common space problems, but it does not remove every kind of whitespace. For example, a nonbreaking space copied from a web page may remain. If a value still behaves unexpectedly after trimming, inspect or replace the specific characters in the source data.

Line breaks, tabs, and other characters that may not be obvious on screen can also affect a count. When checking imported text, compare the original with a cleaned version and inspect suspicious cells closely rather than assuming every invisible character is an ordinary space.

Practical ways to use LEN with other functions

LEN becomes more flexible when paired with another function. For example, you can count ordinary spaces in a cell by comparing the original text length with the length after SUBSTITUTE removes spaces. Assume A2 contains “red maple”; there is one ordinary space, so the formula returns 1:

With “red maple” in A2, =LEN(A2)-LEN(SUBSTITUTE(A2," ","")) returns 1.

This approach counts the regular space character, not every possible whitespace character. If the source contains tabs or nonbreaking spaces, identify those characters separately before relying on the count.

You can use LEN to see whether cleaning changes a value’s length, but interpret the result carefully. For example, if A2 has extra spaces, comparing LEN(A2) with LEN(TRIM(A2)) reveals that TRIM changes the text’s length. It does not reveal which positions contained the extra spaces. To locate and correct a problem, inspect the source, use a cleaning step, or split and examine the text as appropriate for the data.

Another useful pattern is to combine a length test with IF to label entries for review. Assume the expected length is seven and the value is in A2:

With an ID in A2, =IF(LEN(A2)=7,"OK","Check") displays OK for a seven-character value and Check otherwise.

This label is convenient in a review column, but it still checks only the count. A length of seven cannot distinguish a valid code from an unrelated seven-character string.

Common mistakes and troubleshooting

  • Counting what is displayed instead of what is stored: Number formats can show currency signs, commas, or decimal places that are not part of the underlying value. LEN counts the value after it is interpreted as text, not every visual formatting feature.
  • Assuming a blank-looking cell is empty: A formula may return an empty string, or a cell may contain spaces. LEN can help distinguish these cases: spaces have a positive length, while an empty string has length zero. Check the formula bar if the source is unclear.
  • Expecting LEN to ignore spaces: It counts spaces by design. If spaces should not affect a particular check, clean or remove them deliberately and verify the result.
  • Treating matching lengths as matching values: Two different strings can have identical lengths. A length comparison is a useful first check, not a text equality test.
  • Overlooking Unicode details: Some characters that look like one symbol can be stored as multiple Unicode code points. In modern Excel, the treatment of certain surrogate pairs can also depend on the workbook’s compatibility behavior. Accented letters may be represented as one combined character or as a base letter plus a combining mark. As a result, the LEN count can differ from a person’s visual count for emoji or specially composed text.

If a formula is not calculating, check that the cell begins with an equals sign and that the reference points to the intended text. Excel installations configured for some regional settings may require semicolons instead of commas between function arguments; use the separator expected by your installation. LEN itself has only one argument, so a simple cell-reference formula does not require an argument separator.

LEN compared with LENB

LEN is the usual choice when you want the character count of text in an Excel worksheet. Excel also has LENB, a legacy function that counts bytes rather than characters in environments using double-byte character sets. In many settings its result matches LEN, while some East Asian language environments can count certain characters differently.

For ordinary character-length checks, prefer LEN because its purpose is clear and its results are easier to interpret. LENB is relevant only when a specific byte-count requirement or older workbook behavior calls for it. A byte count is not automatically interchangeable with a character count, particularly when text includes characters represented with different byte sizes.

Frequently Asked Questions

Does LEN count spaces in Excel?

Yes. LEN counts ordinary spaces, including spaces at the beginning or end of a cell, as well as spaces between words. A cell with three letters followed by one space has a length of four.

What does LEN return for a blank cell?

LEN returns 0 for an empty cell and for a formula result that is an empty string. A cell containing one or more spaces is not empty for this purpose; those spaces are counted.

Can LEN count characters in a number?

Yes. Excel converts the number to text for the count. For instance, the numeric value 1234 has four characters. Display formatting such as a currency symbol does not generally become part of the stored number that LEN counts.

Why is my LEN result larger than the visible text?

The cell may contain leading or trailing spaces, repeated spaces, a line break, or another character that is difficult to see. The source may also contain Unicode characters that are represented differently from what one visible symbol suggests. Check the source and compare the length before and after appropriate cleanup.

Does LEN tell me whether two cells contain the same text?

No. LEN only reports length, so different values can return the same number. Use a text comparison when you need to test whether two cell contents match; use LEN as a separate check when length is also important.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.