LEN Formula Not Working in Excel? Common Fixes

Errors & fixes · Updated November 20, 2026

If the LEN formula is not working in Excel, first enter =LEN(A1) in a cell formatted as General and replace A1 with the cell you want to check. If Excel displays the formula instead of a result, or the count seems wrong, check the cell format, calculation settings, and whether the text contains spaces or hidden characters.

What LEN does and what it counts

LEN returns the number of characters in a text string. It counts letters, numbers, punctuation, ordinary spaces, and line breaks. The function does not count only visible letters: a space before or after a word contributes to the result just like a letter does.

For example, with the word Excel in A2, the result is 5. With the text Excel␠ (where ␠ represents one trailing space), the result is 6. This is why a count can look too high when a cell appears to contain only a name or code. LEN measures the underlying text, including characters that may be hard to see.

With the text Excel in A2, this formula returns 5:

=LEN(A2)

LEN can also count characters in a number after Excel converts the value to text. It counts the underlying value, not necessarily the way a cell looks after number formatting. For instance, a custom format that adds a currency symbol does not mean that the symbol is stored in the cell and counted by LEN. If you need to measure what a formatted value displays, the problem may require converting it to text with a suitable format rather than applying LEN directly.

Check whether Excel is displaying the formula

If the cell shows =LEN(A1) instead of a number, Excel may be treating the entry as text, or the workbook may have Show Formulas enabled. These are different issues, so check both:

  • Select the formula cell and look at Home > Number Format. If it is set to Text, change it to General, then edit the entry and press Enter. Changing the format alone may not make an existing text entry calculate.
  • Look for an apostrophe before the equals sign in the formula bar. An entry such as ‘=LEN(A1) is stored as text. Remove the apostrophe, then confirm the entry.
  • Check Formulas > Formula Auditing > Show Formulas. If formulas are being shown across the worksheet, turn this option off. The keyboard shortcut on Windows is Ctrl+` (the grave accent); the key location can vary by keyboard layout.
  • Confirm that the entry begins with an equals sign and that the function name is spelled LEN.

After correcting the cell format, edit the formula by selecting the cell, pressing F2, and pressing Enter. On Mac, use Control+U to edit the active cell in many Excel keyboard layouts; if that shortcut differs on your setup, double-click the cell or edit in the formula bar and press Return. The ribbon commands are generally the same in Excel for Windows and Mac, although their placement can vary slightly by version.

Make sure the formula refers to the right cell

A valid LEN formula can return an unexpected result simply because it points to the wrong cell. Click the formula cell and inspect the formula bar. Check that the reference matches the location of the text you intended to count, especially if you copied the formula down a column. Relative references adjust as they are copied: a formula referring to A2 will normally refer to A3 in the next row.

For a list of entries in A2:A20, put this formula in B2 and fill it down to B20:

=LEN(A2)

In B2, the formula counts A2; in B3, the copied formula counts A3. If you expected every row to refer to one fixed cell, use an absolute reference instead. With the text to count in A1, this formula keeps the reference fixed when copied:

=LEN($A$1)

Also check whether the source cell contains a formula that returns text, or whether the text is in a neighboring column. LEN evaluates the cell reference you give it; it does not search for the nearest populated cell. If the source cell contains an error, LEN returns that error rather than a character count.

Remove extra spaces without changing the source data

When a count is one or more characters higher than expected, ordinary spaces are a common cause. TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to a single space. It does not remove every type of whitespace, and it changes the text that it returns, so use it only if that cleanup matches your goal.

With the text to clean in A2, this formula counts the characters after TRIM has removed extra ordinary spaces:

=LEN(TRIM(A2))

For example, if A2 contains two spaces before Cat and one after it, the original count is 6 spaces and letters combined; the formula returns 3. If the text is a full name with two spaces between the first and last name, TRIM reduces those spaces to one rather than deleting the separator altogether.

If you want to count characters while ignoring every ordinary space, use SUBSTITUTE instead. With the original text in A2, this formula removes ordinary spaces from the text before counting it:

=LEN(SUBSTITUTE(A2," ",""))

This is not the same as trimming. It removes spaces between words too, so a value such as New York is counted as NewYork. Keep the original data unchanged by placing the formula in another cell; it only changes the value returned by the formula, not the source in A2.

Look for nonprinting and nonbreaking characters

Text copied from websites, PDFs, email, or other systems can contain characters that look like spaces or do not display clearly. CLEAN removes many nonprinting ASCII control characters, such as some characters associated with line breaks, but it does not remove every invisible Unicode character. TRIM and CLEAN therefore cannot be assumed to fix all pasted text.

With the text in A2, this formula counts the characters remaining after CLEAN and TRIM are applied:

=LEN(TRIM(CLEAN(A2)))

A nonbreaking space is a frequent exception. It can look like an ordinary space but is not removed by TRIM. If you have confirmed that the unwanted character is a nonbreaking space, use SUBSTITUTE with CHAR(160). With the text in A2, this formula replaces those characters with nothing before counting:

=LEN(SUBSTITUTE(A2,CHAR(160),""))

That formula removes nonbreaking spaces only; it does not remove ordinary spaces or every possible hidden character. For mixed or unfamiliar data, compare the original count with the cleaned count and inspect a sample before applying a cleanup to a full column. If a pasted value includes line breaks that you want to preserve, do not use a cleanup formula until you know how it affects the result you need.

Check calculation settings and formula errors

If LEN returns an old result after the source text changes, the workbook may be set to manual calculation. In Excel for Microsoft 365 on Windows, choose Formulas > Calculation Options > Automatic. The corresponding calculation options are also available on Mac, though the ribbon layout can differ. You can also recalculate the workbook with F9 on Windows; Mac keyboard behavior can depend on the keyboard and function-key settings.

Check the displayed error as well as the formula:

  • #NAME? usually indicates that Excel does not recognize something in the formula, such as a misspelled function name. The English function is LEN, not LENGTH.
  • #REF! indicates a broken cell reference, often because a referenced row or column was deleted.
  • #VALUE! or another error may be coming from the referenced cell. LEN passes through an error in its input rather than turning it into a character count.

In some regional settings, Excel uses semicolons instead of commas between function arguments. LEN itself takes one argument, so its basic formula has no separator. If you use a multi-argument cleanup formula and Excel rejects the commas, your regional list-separator setting may require semicolons. Excel may also show localized function names in some language versions. Do not change regional settings just to fix a one-argument LEN formula.

Distinguish a genuinely empty cell from an empty result

A cell that looks blank is not always empty. It may contain a formula that returns an empty string, a space, or an invisible character. A space has a length of 1, while an empty string has a length of 0. A formula returning an empty string can therefore produce a LEN result of 0 even though the cell is not truly blank.

With a value or formula result in A2, this formula checks whether its length is zero:

=LEN(A2)=0

The result is TRUE when the evaluated text has zero characters and FALSE when it has one or more. It does not establish that A2 is an unused cell: a formula that returns an empty string also has a length of zero. If you specifically need to test whether a cell contains anything at all, use a separate check suited to that purpose rather than treating LEN as a test for cell emptiness.

When LEN is the right tool—and when it is not

LEN is useful for validating character limits, checking whether imported codes have unexpected lengths, and comparing cleaned versus original text. It counts the characters Excel receives; it does not determine whether a value is valid for a particular system. A code can have the expected length and still contain the wrong characters, so length checks are only one part of data validation.

LEN also differs from LENB. LENB counts bytes in language environments that use double-byte character sets, while LEN counts characters. For most everyday text checks, LEN is the appropriate function. Use LENB only when a process specifically requires a byte count and you have confirmed how the workbook’s language and version handle that requirement.

If your goal is to display the characters in a cell, use the cell itself rather than LEN; LEN returns a number, not a cleaned or shortened string. If your goal is to remove unwanted characters, use an appropriate cleaning or replacement formula in a separate cell, then check its length. Keeping the original beside the cleaned result makes it easier to find out whether a formula removed too much.

Frequently Asked Questions

Why does LEN count spaces in Excel?
Spaces are characters, so LEN includes leading, trailing, and internal spaces. Use TRIM to normalize ordinary extra spaces, or SUBSTITUTE if you deliberately want to remove every ordinary space before counting.

Why does Excel show the LEN formula instead of the answer?
The cell may be formatted as Text, the formula may begin with an apostrophe, or Show Formulas may be enabled. Change the cell to General and re-enter the formula, remove any leading apostrophe, and check Formulas > Formula Auditing > Show Formulas.

Does LEN count a formula that returns an empty string?
LEN returns 0 for an empty string, including a formula result of empty text. That result does not mean the cell itself is blank; the cell can still contain a formula.

Why does TRIM not fix my LEN result?
TRIM handles ordinary spaces, not every character that resembles a space. Nonbreaking spaces and other invisible characters may remain. If you identify nonbreaking spaces in the source, SUBSTITUTE with CHAR(160) can remove them.

Can LEN count the characters shown by cell formatting?
Not necessarily. LEN works with the underlying cell value, while number formats can change how a value appears without changing what is stored. If you need to count a formatted display, convert the value to text using a method that matches the required format, then count that text.

Related

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.