How to filter cells by length of text in Excel?

Filtering cells by text length in Excel is a common need when processing and analyzing data. By filtering for text of a specific length, you can quickly locate content with a certain number of characters, such as limiting the length of input or extracting key data. Here are several methods to accomplish this, whether using built-in features or formulas, to make the task simple and efficient.

• Filter cells with text length equal to a specific number using Filter feature
• Easily filter cells by text length (equal to / less than / greater than) using Kutools for Excel
• Filter cells by text length (equal to / less than / greater than) using helper column
• Filter cells by text length (equal to / less than / greater than) using FILTER function

Filter cells by given text length with Filter command

You can filter cells by given text length (the number of characters) with Text Filter’s Custom Filter function. Please process as follows:

1. Select the column data that you want to filter, and click the "Filter" button under "Data" tab.
2. Click the "Filter Arrow", and move mouse over the "Text Filters" item, and choose the "Custom Filter" item. See the following screenshot:
   
3. In the popping up "Custom AutoFilter" dialog box, specify the criteria as "Equals", and enter 16 question marks to indicate the length.:
   
4. Click "OK", the cells with 16 characters in the column are filtered.

Easily filter cells by text length (equal to / less than / greater than) using Kutools for Excel

In this section, we’ll explore how to use "Kutools for Excel" to filter cells based on text length—whether equal to, less than, or greater than a specific number, with just a few clicks—no formulas or helper columns required.

1. Select the range of data you want to filter. Click "Kutools Plus" > "Super Filter" to open the "Super Filter" pane.
2. In the pane, set the filter criteria in the criteria box as you need:
   In the first criteria box, select the column header that you want to filter;
   In the second criteria box, choose "Text";
   In the third criteria box, select the condition: "Length is" , "Length is greater than", or "Length is less than".
   In the last criteria box, enter the desired text length (e.g., "16" for exactly 16 characters).
   
3. Then, click "Filter" button apply the conditions. The results will be displayed in the selected range. See screenshot:
   

Filter cells by text length (equal to / less than / greater than) using helper column

In this section, we’ll walk you through how to set up a helper column, calculate text lengths using the LEN function, and apply filters to extract the desired data. This method is simple and effective for scenarios where you need to identify cells with text that is equal to, less than, or greater than a specific number of characters.

1. In a help column, enter the below formula, and then, drag the fill hanlde down to fill the formula to other cells. All charatcer length of each cell is calclated.

   =LEN(A2)

   
2. Select the helper column, and click the "Filter" button under "Data" tab.
3. In the drop down box, select "Number Filters", and then, choose the criteira that you need from the expanded list box, see screenshot:
   
4. In the following "Custom Autofilter" box, set the number of characters that you want to filter based on, see screenshot:
   
5. Click "OK" to get the filter result.

Filter cells by text length (equal to / less than / greater than) using FILTER function

If you have Excel 365 or Excel 2021 and later versions, its new FILTER function is a powerful tool for dynamic filtering of data based on specific conditions. One of its versatile applications is filtering cells by the length of text, whether equal to, less than, or greater than a certain number of characters.

● Filter Cells with Text Length Equal to a Specific Number

To filter cells where the text length is exactly 15 characters, please apply the below formula, and press Enter key to get the result:

=FILTER(A2:A19, LEN(A2:A19)=15, "No Match")

● Filter Cells with Text Length Less Than a Specific Number

To filter cells with text length less than 15, please apply the below formula:

=FILTER(A2:A19, LEN(A2:A19)<15, "No Match")

● Filter Cells with Text Length Greater Than a Specific Number

To filter cells with text length greater than 15, please apply the below formula:

=FILTER(A2:A19, LEN(A2:A19)>15, "No Match")

● Filter Cells Within a Range of Text Lengths

To filter cells with text length greater than 16 but less than 20:

=FILTER(A2:A19, (LEN(A2:A19)>16)*(LEN(A2:A19)<20), "No Match")

In summary, filtering cells by text length in Excel can be accomplished through various methods, each catering to different levels of complexity and user preferences. By choosing the method that best suits your needs, you can efficiently organize and analyze your data with minimal effort. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.