Excel Filter by Text Length: Steps and Formulas

Formulas & functions · Updated November 19, 2026

To filter Excel rows by the number of characters in a text cell, add a helper column with =LEN(A2), fill it down, and filter that column with a number rule. Excel’s standard text-filter menu does not include a direct “length of text” condition, so the helper column is the simplest reliable method; Microsoft 365 users can also return matching rows with a dynamic formula.

Why filtering by length needs a helper

Excel’s built-in AutoFilter offers useful text conditions such as Begins With, Contains, and Equals, but it does not provide a condition such as “has more than 12 characters.” A text value’s length is calculated rather than stored as a separate property that AutoFilter can select directly. A helper formula makes that calculation visible in a regular worksheet column, where Excel’s numeric filter options can act on it.

This approach is useful for many data-cleaning tasks: finding short product codes, flagging unusually long names, checking whether imported identifiers have a required number of characters, or isolating descriptions that exceed a preferred length. The original text remains unchanged; the helper column simply records a number for each row.

Add a LEN helper column

Suppose the text you want to evaluate is in A2:A100, with a header in A1. Put a label such as “Character count” in B1. In B2, enter =LEN(A2), then copy the formula down alongside all the records. The result in each row is the number of characters in that row’s A-cell value. For example, if A2 contains “Blue mug,” B2 returns 8 because the space counts as a character.

  1. Insert a new column next to the text column, or use an empty column in the same data range. Give the column a clear header.
  2. Enter the LEN formula in the first data row. If the source text is in a different column, change A2 to the appropriate cell reference.
  3. Fill the formula down to the last record. You can drag the fill handle, or select the formula cell and the cells below it and press Ctrl+D on Windows. On Mac, use Command+D.
  4. Click a cell in the data range and choose Data > Filter. Excel adds filter arrows to the header row.
  5. Open the arrow on the helper column and select Number Filters, then choose the comparison you need.

Keep the helper values within the same contiguous range as the data, and make sure each row’s count stays beside its corresponding record. If your data is an Excel table, adding the formula in the table’s new column usually fills it down for the table rows and keeps the helper column included as the table grows.

Filter for a minimum, maximum, or exact length

Once the helper column contains numbers, choose a numeric condition that matches the question. To find text longer than 10 characters, open the helper-column filter, select Number Filters > Greater Than, and enter 10. “Greater Than” excludes values of exactly 10; choose Greater Than or Equal To if 10-character entries should qualify too.

For a maximum, use Number Filters > Less Than or Number Filters > Less Than or Equal To. For one precise length, choose Number Filters > Equals. These rules can be combined when you need a range: choose Number Filters > Between and enter the lower and upper values. For example, Between 6 and 12 includes both 6 and 12. If you want to exclude those endpoints, use a custom combination of greater-than and less-than conditions instead.

The filter hides rows that do not meet the condition; it does not delete them. Clear the filter from the helper-column menu to show the full list again. You can also filter another column at the same time—for example, show only product names longer than 10 characters and only records from a particular category.

Return matching rows with a formula

If you want a separate, automatically updating list rather than hiding rows in the original data, Excel for Microsoft 365 and Excel 2021 or later can use the FILTER function. With complete records in A2:C100 and the text to check in A2:A100, enter this formula in an empty cell outside the source range:

=FILTER(A2:C100,LEN(A2:A100)>10,"No matches")

This returns the rows whose column A values have more than 10 characters; if none qualify, it displays “No matches.” Change the comparison to LEN(A2:A100)=10 for exactly 10 characters, or use LEN(A2:A100)<=10 for 10 or fewer. The ranges supplied to FILTER must cover corresponding rows. Leave enough blank space below and to the right of the formula for the results to spill; a value blocking that output area causes a #SPILL! error. This formula produces a results list and does not switch on AutoFilter or hide source rows.

FILTER is available in Microsoft 365 and Excel 2021 or later, including supported current versions of Excel for Mac and the web. In older Excel versions without FILTER, use the helper-column method and AutoFilter instead. If you add records beyond row 100, expand the formula’s ranges or use an Excel table so the source and criteria ranges include the new rows.

What LEN counts as text

LEN counts characters in the cell value, including ordinary spaces, punctuation, and line breaks. It does not count the cell’s formatting, column width, or the number of pixels a string occupies on screen. Thus, a narrow “i” and a wide “W” each count as one character. The formula is measuring character count, not visual length or the number of words.

Leading and trailing spaces count too. If a cell appears to contain “Mug” but has an extra space at the end, LEN returns 4 rather than 3. That difference is sometimes intentional, especially when checking whether imported data has padding; in other cases, it signals a cleanup issue. LEN also returns 0 for a genuinely empty cell. If a formula in a cell displays an empty string, its result is also treated as zero length by LEN.

For numbers, Excel evaluates the value as text for LEN, and the result is based on the value rather than how a number format displays it. For example, a numeric value displayed with leading zeros by a custom format does not necessarily have those displayed zeros in its underlying value. If leading zeros are part of an ID and must count, store the ID as text rather than as a number.

Common problems and fixes

  • The filter arrow is missing: Check that the header row and data form one continuous range, then choose Data > Filter. A blank row or blank column inside the records can cause Excel to detect only part of the data.
  • The results seem one character too long: Check for leading, trailing, or repeated spaces. Clicking the cell and looking in the formula bar can reveal some spaces, though trailing spaces may be hard to see.
  • Blank-looking cells pass a rule unexpectedly: A cell that looks blank may contain spaces, or a formula that returns an empty string. LEN counts spaces, while an empty-string result has length zero. Decide whether spaces should be considered meaningful before filtering.
  • The helper column is not filtered with the records: Make sure it is part of the same data range and has a header. Avoid leaving an empty column between the records and the helper column.
  • The formula shows as text instead of calculating: Check that the cell is not formatted as Text, and that the entry begins with an equals sign. Change the format to General if needed, then re-enter the formula.
  • The dynamic formula returns #SPILL!: Clear cells in its intended output area. Also check that the result is not being entered inside an Excel table, where dynamic-array spill behavior is restricted.

If the data comes from another system, unusual whitespace or control characters can also affect the count. Do not remove characters automatically unless you know they are unwanted: a space or line break may be meaningful in the source. Make a copy of the data before applying broad cleanup, and compare a few sample values against the original source.

Choose the method that fits your task

Use a helper column and AutoFilter when you want to inspect, sort, or work with the original records in place. It is straightforward, works in older desktop versions, and makes each row’s count easy to check. Use FILTER when you need a separate list that updates as source values change and your Excel version supports dynamic arrays. The formula route is compact, but the helper route is often easier for a team to understand and maintain.

For a one-time check, a helper column is usually preferable to trying to construct a complicated custom filter. For a recurring report, consider keeping the helper column in an Excel table: its formula can extend with added table rows, and the filter controls remain attached to the headers. Regardless of the method, confirm whether the rule should include its boundary—for example, “more than 10” is different from “at least 10.”

Frequently Asked Questions

Can I filter text by length without adding a helper column?
Not directly with the standard AutoFilter text conditions. Use a LEN helper column for the normal filter arrows, or use FILTER in a supported Excel version to create a separate matching list.

How do I filter cells with exactly 8 characters?
Calculate the length with LEN, then set the helper-column filter to Number Filters > Equals and enter 8. With text in A2:A100, a dynamic-array alternative is =FILTER(A2:A100,LEN(A2:A100)=8,"No matches").

Does LEN count spaces?
Yes. Ordinary spaces, including spaces at the start or end of a cell, count as characters. A line break in a cell also contributes to the count.

Can I filter for text between two lengths?
Yes. With a LEN helper column, use Number Filters > Between and enter the lower and upper counts. The Between condition includes both endpoints, so choose a different comparison if either endpoint should be excluded.

Why does an ID with leading zeros have the wrong length?
If Excel stored the ID as a number, leading zeros may be display formatting rather than part of the value, so LEN will not count them. Store identifiers as text when the zeros are meaningful characters, then calculate their length.

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.