Excel LEN Formula Examples: Count Text in Cells

In Excel for Microsoft 365, use =LEN(A2) to count the characters in cell A2, including spaces and punctuation. LEN is useful for checking text length, validating entries, counting spaces, and spotting data that needs cleanup; the examples below show how to adapt it to each task.

What the LEN function counts

LEN returns the number of characters in a text string. It counts letters, digits, punctuation, and spaces, including spaces at the beginning or end of a cell. It also counts a line break inside a cell as a character. LEN does not count the cell itself, its formatting, or the width of the column.

For a basic example, suppose A2 contains the text Excel. With that cell layout, enter =LEN(A2); the result is 5. If A2 contains Excel LEN, the same formula returns 9: five letters in Excel, one space, and three letters in LEN. This makes LEN a good first check when text looks right on screen but may contain unnoticed spaces.

LEN also works directly with a quoted text string. With no cell reference needed, =LEN("Hello!") returns 6. In a worksheet, a reference is usually more practical because the source text can change without editing the formula.

A blank cell returns 0 when passed to LEN. A formula that returns an empty string, written as two quotation marks with nothing between them, also has a length of zero. A cell containing a space is different: its LEN result is 1, even though the cell may look blank.

Basic LEN formula examples

To count text down a column, put a formula beside the first entry and fill it down. For example, with product descriptions in A2:A20, enter =LEN(A2) in B2, then copy or fill the formula through B20. Each row reports the length of the corresponding description. In Microsoft 365 for Windows, drag the fill handle at the lower-right corner of B2 down the column, or copy B2 and paste into the destination cells. The formula adjusts its reference by row.

LEN can also be used as a test. With a short example string in A2, namely Quarterly report notes, the text has 22 characters. With that content in A2, =IF(LEN(A2)>20,"Long","Short") returns Long. The comparison can be changed to match a form rule, such as requiring a minimum number of characters or flagging entries that exceed a limit.

To check whether a value has exactly ten characters, with the value in A2, use =LEN(A2)=10. The result is TRUE if the length is ten and FALSE otherwise. This checks length only: it does not establish that the text contains the right kind of characters. For example, it would accept ten spaces or ten punctuation marks, so combine it with other checks when the content has a specific format.

When applying the same check to many rows, keep the formula beside the source data rather than replacing the original values. That makes it easier to review which entries fail and correct them without losing the submitted text.

Count or remove spaces

Spaces are a common source of confusion because leading and trailing spaces are easy to miss. If A2 contains two leading spaces, North, and one trailing space, with the cell content exactly as shown here, =LEN(A2) returns 8. North has five letters, and the three spaces are counted too.

To count ordinary spaces in a cell, subtract the length after removing spaces from the original length. With the text red blue green in A2, =LEN(A2)-LEN(SUBSTITUTE(A2," ","")) returns 2, because there are two spaces. SUBSTITUTE replaces each ordinary space with nothing; LEN measures the difference. This method counts regular space characters, not every possible whitespace character, such as tabs or some nonbreaking spaces.

To count characters after cleaning extra ordinary spaces, use TRIM inside LEN. With A2 containing two spaces before North, three between North and District, and one after District, =LEN(TRIM(A2)) returns 14. TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. North District is five letters, one space, and eight letters, for a total of 14.

TRIM is not a universal cleanup tool. Data copied from websites or other systems can contain nonbreaking spaces, which look like ordinary spaces but may not be removed by TRIM. If a cleaned length still seems wrong, inspect the source text and identify the actual characters rather than repeatedly applying TRIM.

Count a specific character or text fragment

The same length-difference method can count a particular character. With A2 containing the text committee, =LEN(A2)-LEN(SUBSTITUTE(A2,"m","")) returns 2, because removing every lowercase m reduces the string by two characters. This comparison is case-sensitive in the sense that lowercase m and uppercase M are different characters.

To count a text fragment, put the text being searched for in B2. With banana in A2 and an in B2, =(LEN(A2)-LEN(SUBSTITUTE(A2,B2,"")))/LEN(B2) returns 2. The formula measures how many characters were removed, then divides by the fragment’s length. Make sure B2 is not empty, or the division would be invalid. This approach counts non-overlapping matches, which is how SUBSTITUTE replaces repeated instances.

For a formula intended to count one character, the simple length difference is often easiest to read. For a longer fragment, divide by the fragment length as shown above. If the text being counted can occur with different capitalization, decide whether those versions should be treated as the same before choosing a method; SUBSTITUTE distinguishes the actual text being replaced.

Use LEN to validate and clean data

LEN is useful for checking identifiers and other fields with a stated character limit. It can reveal a missing digit, an extra space, or an unexpectedly long entry. However, a length check alone cannot verify a pattern. If an identifier must start with a prefix, contain digits, or follow a fixed structure, check those requirements separately as well.

For instance, suppose A2 should contain exactly ten characters. With the candidate value in A2, =IF(LEN(A2)=10,"OK","Check") returns OK when the count is ten and Check otherwise. If an identifier should have a fixed prefix too, use additional tests for that prefix; do not treat a matching length as proof that the whole identifier is valid.

A related issue occurs when identifiers contain leading zeros. If 00123 is entered as a number, Excel stores it as 123, so LEN sees three digits. A number format may display 00123 without changing the stored value. When A2 contains the numeric value 123 and the intended display is five digits, =LEN(TEXT(A2,"00000")) returns 5. For codes where leading zeros are part of the actual value, storing them as text is often safer than relying only on display formatting.

Numbers, dates, and times are stored as numeric values even when their cells display in a formatted way. LEN works from the underlying value converted to text, not necessarily from the formatted appearance. A date displayed as 1/1/2024, for example, is not counted as those displayed characters by a plain LEN reference. If the goal is to count a formatted representation, convert it to text with an appropriate TEXT format first, then count that text.

Common mistakes and limits

Counting visible characters instead of stored characters: A cell may look empty but contain spaces. Check with LEN, and compare results before and after TRIM.
Assuming formatting changes the value: A custom number format can add displayed zeros or separators without changing the stored number that LEN evaluates.
Confusing characters with bytes: LEN is a character-counting function, not a general-purpose measure of file size or encoded bytes.
Expecting TRIM to remove every invisible character: It handles ordinary spaces in a particular way, but other whitespace characters can remain.
Using a length check as complete validation: The right number of characters does not guarantee the right letters, digits, or sequence.

Most ordinary English text behaves as readers expect: each letter, digit, space, or punctuation mark contributes one to the count. Some Unicode characters, including certain emoji and combined symbols, can behave differently depending on the character and workbook compatibility behavior. If exact counts for those characters matter, test representative entries in the target workbook rather than assuming every symbol will count as one visible glyph.

Formula separators can also depend on Excel’s regional settings. The examples use commas between function arguments, as in the standard English-language syntax. If Excel rejects a formula despite the function and references being correct, the installation may expect semicolons as argument separators.

LEN compared with other counting approaches

LEN is the right choice when the question is how many characters are in one text value. It is different from COUNT, which counts numeric cells in a range, and COUNTA, which counts nonempty cells. Neither COUNT nor COUNTA reports the number of characters inside a cell.

TRIM and SUBSTITUTE are not replacements for LEN; they alter or transform text so LEN can measure a particular version of it. Use LEN alone for the stored text length, LEN with TRIM for a length after ordinary-space cleanup, or the difference between two LEN results to count characters removed by SUBSTITUTE.

Excel’s LENB function is intended for byte-oriented counting in languages that use double-byte character sets. It is not a reliable general way to calculate a text file’s size or the number of UTF-8 bytes. For typical worksheet checks, LEN is the more direct function; use a byte-specific approach only when the data format and encoding requirements are known.

Frequently Asked Questions

Does Excel LEN count spaces?
Yes. LEN counts leading, trailing, and internal spaces. A cell containing one ordinary space has a length of 1, while a truly empty cell has a length of 0.

How do I count characters without spaces?
With the text in A2, use =LEN(SUBSTITUTE(A2," ","")). This removes ordinary spaces before counting. It does not remove tabs or every type of whitespace character.

Why does LEN show a different number from what I see?
The cell may contain hidden leading or trailing spaces, or it may display a formatted number or date whose stored value differs from what appears on screen. Unicode symbols can also complicate counts. Check the underlying value and test cleanup or text conversion as appropriate.

Can LEN count letters only?
No. LEN counts all characters, including digits, spaces, and punctuation. To count only a particular character, compare the original length with the length after SUBSTITUTE removes that character.

Does LEN count a blank cell as zero?
Yes. An actually empty cell returns 0, and a formula result of an empty string also has a length of zero. A cell containing a space is not empty for this purpose and returns 1.

Is LEN available on Excel for Mac and the web?
Yes. LEN is a standard Excel function and the same formula syntax is used in Microsoft 365 for Windows, Excel for Mac, and Excel for the web. Regional settings can affect which argument separator Excel expects.

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.