Hur filtrerar man celler efter textlängd i 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

Filtrera celler efter given textlängd med filterkommandot

Du kan filtrera celler efter angiven textlängd (antalet tecken) med textfilterets anpassade filterfunktion. Gör så här:

1. Select the column data that you want to filter, and click the Filter knapp under Data fliken.
2. Klicka på Filter Arrow och flytta musen över Textfilter och välj Anpassat filter item. See the following screenshot:
   
3. I poppar upp Anpassat autofilter dialog box, specify the criteria as lika, and enter 16 question marks to indicate the length.:
   
4. Klicka 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 för 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 > Superfilter att öppna Superfilter rutan.
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: Längd är , Length is greater than, eller Length is less than.
   In the last criteria box, enter the desired text length (e.g., "16" for exactly 16 characters).
   
3. Klicka sedan 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 knapp under Data fliken.
3. In the drop down box, select Antal filter, and then, choose the criteira that you need from the expanded list box, see screenshot:
   
4. I följande Custom Autofilter box, set the number of chanratcers that you want to filter based on, see screenshot:
   
5. Klicka 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, vår webbplats erbjuder tusentals tutorials.