Excel LEN Function Examples for Text, Spaces, and Data

Excel’s LEN function returns the number of characters in a value, including spaces. For example, if A2 contains the text “North wind,” enter =LEN(A2) in another cell to get 10; use LEN to check text length, spot extra spaces, or count occurrences of a character with a related formula.

What the LEN function does

LEN is a text function available in Excel for Microsoft 365 on Windows. Its syntax is simple: LEN(text). The single argument can be a cell reference, a text string in quotation marks, or another expression that produces a value. LEN returns a number, so you can display the result directly or use it in a comparison, calculation, or other formula.

LEN counts the characters in the value passed to it, not the width of the cell or the way the cell looks on the worksheet. Ordinary letters, digits, punctuation marks, and spaces all contribute to the count. A genuinely empty cell and a formula that returns an empty string have a length of zero. A cell containing one space is not empty: its length is one.

For example, with no worksheet data required, the formula =LEN("Excel") returns 5. The formula =LEN("Excel is fun") returns 12: the letters account for 10 characters, and the two spaces account for the other two. These small examples are useful for understanding why a count may be higher than the number of visible letters.

Basic LEN formula examples

To try LEN on worksheet data, type a value in a cell and refer to that cell in the formula. The following examples show common patterns. Enter each formula in a different cell from the source data so that it does not replace the text being measured.

With the word Excel in A2, enter =LEN(A2). The result is 5.
With the phrase Red maple in A2, enter =LEN(A2). The result is 9, counting the space between the words.
With the text 00427 in A2, enter =LEN(A2). The result is 5 if the value is stored as text, preserving both leading zeros.
With A2 empty, enter =LEN(A2). The result is 0.

These examples also show why it matters whether a value is stored as text or as a number. If a code begins with zero, Excel may interpret it as a number and discard the leading zero when you enter it. LEN cannot count a zero that is no longer part of the stored value. For identifiers such as product codes or postal codes, store the value as text, or use an appropriate number format and a TEXT formula when you need a fixed-width text result.

Counting spaces and cleaning text

Spaces often explain unexpected LEN results. A value may have leading spaces before the first letter, trailing spaces after the last letter, or multiple spaces between words. Those characters are included in the count even when the extra spacing is hard to notice on screen. This is a common reason that two apparently identical entries produce different results.

With A2 containing two leading spaces, the word East, and one trailing space, =LEN(A2) returns 7. That total is two leading spaces, four letters, and one trailing space. To measure the text after removing ordinary extra spaces, use TRIM. With that same value in A2, =LEN(TRIM(A2)) returns 4. TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to a single space.

TRIM is not a universal cleanup tool. It targets the standard space character and does not necessarily remove nonbreaking spaces or every other invisible character that may arrive from a website or copied document. If LEN remains unexpectedly high after TRIM, inspect the source or use a cleanup method suited to the particular character. Replacing characters without identifying them can also remove meaningful punctuation or spacing.

Count how many times a character appears

LEN can help count occurrences when combined with SUBSTITUTE. The idea is to calculate the original length, remove every occurrence of the character you want to count, calculate the shorter length, and subtract. If A2 contains AB-12-XY, the formula =LEN(A2)-LEN(SUBSTITUTE(A2,"-","")) returns 2 because removing both hyphens shortens the string by two characters.

This pattern works for ordinary characters and spaces. With A2 containing the text red blue green, =LEN(A2)-LEN(SUBSTITUTE(A2," ","")) returns 2 because there are two ordinary spaces. To count a letter, replace the space in the formula with that letter. SUBSTITUTE replaces all matching instances by default, which is why the difference in lengths gives the total number of matches.

Excel text comparisons in many functions are not case-sensitive, so this approach generally counts a letter regardless of whether it appears in uppercase or lowercase. It is designed for a single character or exact text fragment; it does not count a character that is merely similar in appearance but has a different underlying code. If the text can contain special Unicode characters, confirm how those characters are represented before relying on a character count.

Use LEN to check required text lengths

A useful data-quality check is to compare a value’s length with an expected length. For instance, with an identifier in A2 that should contain exactly eight characters, enter =LEN(A2)=8. The result is TRUE when the value has eight characters and FALSE otherwise. This is a quick way to flag short or long entries in a helper column.

To show a more readable message, use IF. With the same eight-character requirement and the value in A2, =IF(LEN(A2)=8,"Valid","Check") returns Valid when the length is exactly eight and Check otherwise. The formula checks length only: it does not verify that the value contains the right type of characters, is a real account number, or meets any other business rule.

If the requirement is a minimum or maximum rather than an exact length, use a comparison such as LEN(A2)>=8 or LEN(A2)<=12 within a formula. For more reliable data entry, you can also use Excel’s Data Validation feature to restrict what users enter, but the correct rule depends on the field. A length check alone may accept spaces, punctuation, or letters when a field is supposed to contain digits only.

Numbers, dates, and displayed formatting

LEN can accept numbers, but its result is based on the value Excel passes to the function as text—not on every visual detail of the cell’s display. With the number 123 in A2, =LEN(A2) returns 3. If A2 contains a numeric value displayed with a custom format that adds leading zeros, those formatting zeros are not necessarily characters in the underlying value. A cell’s visible appearance and the text produced by a formula are not always the same thing.

For example, if A2 contains the number 123 and you need a five-character text version with leading zeros, use =LEN(TEXT(A2,"00000")). The result is 5 because TEXT converts the value to the text 00123 before LEN counts it. This is useful for building a code in a formula, but it does not change the original value in A2. If the code itself must retain leading zeros as entered, consider storing it as text from the start.

Dates require similar care. Excel stores dates as serial numbers and applies a date format to display them. LEN applied directly to a date cell counts the text representation of the underlying value used by the formula, not necessarily the visible date such as 11/2/2026. To count a date in a specific text format, convert it explicitly first. With a date in A2, =LEN(TEXT(A2,"yyyy-mm-dd")) returns 10 for the formatted text 2026-11-02. Choose the format that matches the text you actually need to measure.

Common mistakes and troubleshooting

If the result is one or more characters higher than expected, check for spaces at the beginning or end of the value, repeated spaces between words, or invisible characters copied from another source. Comparing LEN(A2) with LEN(TRIM(A2)) is a quick first check for excess ordinary spaces. If the two results match, that does not prove the text has no invisible characters; it only means TRIM did not change the length.

Another common misunderstanding is expecting LEN to count what is displayed. A number format can show currency symbols, decimal places, or leading zeros without changing the stored number. A date format can display slashes or month names even though Excel stores the date as a serial value. Use TEXT when the length of a particular displayed representation is what matters, and make the desired format explicit.

Unicode characters can need special consideration. In current Excel, how LEN counts certain supplementary Unicode symbols, including some emoji, can depend on the workbook’s compatibility behavior. A symbol that looks like one glyph may be represented by more than one underlying code unit, and some visible symbols are combinations of multiple characters. If exact counts matter for international or emoji-rich text, test with representative data in the Excel version and workbook you use rather than assuming that one visual symbol always equals one LEN character.

Finally, check that the formula refers to the intended cell and that the source really contains the text you expect. A value imported from another system may include nonbreaking spaces or other characters that are visually difficult to distinguish from ordinary text. Copying the cell into a plain-text editor or using targeted replacement can help identify a source issue, but preserve a copy of original data before making bulk changes.

LEN compared with related functions

LEN answers “how many characters?” It does not return the characters themselves, remove spaces, or search for a word. Use LEFT, RIGHT, or MID when you need to extract part of a text value; use FIND or SEARCH when you need the position of text; and use TRIM when you need to clean ordinary extra spaces. These functions solve related but different problems.

LENB is the byte-count counterpart in Excel environments that support double-byte character sets. Its purpose is different from LEN, which counts characters. For many users and ordinary English text, LEN is the appropriate choice. If working with languages or data systems where byte length matters, check the behavior supported by the Excel language and version in use; a byte count should not be treated as a character count.

For a basic length check, LEN is usually clearer than counting characters manually or using a longer formula. For occurrence counts, the LEN-and-SUBSTITUTE pattern is compact and does not require a newer dynamic-array function. The best choice depends on the question: length, cleanup, extraction, position, and occurrence count are distinct tasks, even when they all involve text.

Frequently Asked Questions

Does Excel LEN count spaces?
Yes. LEN counts ordinary spaces in the value, including leading, trailing, and repeated spaces. Use TRIM when you want to remove ordinary extra spaces before measuring, while remembering that TRIM may not remove every type of invisible whitespace.

What does LEN return for a blank cell?
For a genuinely blank cell, LEN returns 0. It also returns 0 for a formula result that is an empty string. A cell containing a space is not blank for this purpose, so LEN returns 1.

Can LEN count letters or spaces in a sentence?
LEN returns the total character count, including letters, punctuation, and spaces. To count one specific character, subtract the length after SUBSTITUTE removes that character from the original length.

Why does LEN give a different result from what I see?
Extra spaces or invisible characters may be present, or the cell may display a formatted number or date whose appearance differs from its underlying value. Compare the raw value, try TRIM for ordinary spaces, and use TEXT if you need to measure a particular displayed format.

Does LEN count numbers and dates?
Yes, but LEN measures the text representation of the value passed to it, not necessarily the formatting shown in the cell. Use TEXT to create a specific text representation first when you need to count a fixed date or number format.

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.