top of page
Search

Excel Filter Contains Text: How to Filter by Keyword (Partial Match)

Excel can already filter text that contains a keyword…but what if you didn’t have to do it manually every time?

For example, your data might include values like “Apple”, “Green Apple”, or “Apple Juice”. While the standard filter can find these, you need to reapply it each time — and it doesn’t update dynamically.

In this guide, you’ll learn how to create a more flexible solution using an Excel filter that contains text formula, so your results update instantly based on a keyword.


Prefer to watch instead? Here’s a quick walkthrough:

What you’ll learn:

  • Filter data based on a keyword

  • Return results with partial matches

  • Do it without helper columns


How to filter data that contains text in Excel

The Formula

In this example, the data is stored in an Excel Table named Keyword_Filter, and we’re searching within the Product_Name column.

=FILTER(Keyword_Filter, ISNUMBER(SEARCH(N9, Keyword_Filter[Product_Name])), "No Match")

💡 The keyword is entered in cell N9, so you can change it anytime without editing the formula.

If you're using a normal range instead of a table:

=FILTER(A2:B100, ISNUMBER(SEARCH(N9, A2:A100)), "No Match")

How It Works

  • SEARCH(N9, Keyword_Filter[Product_Name])

    Looks for the keyword entered in cell N9 within the Product_Name column.

    → Returns a number if the keyword is found

    → Returns an error if not found

  • ISNUMBER(...)

    Converts results into TRUE / FALSE

    → TRUE = contains “Apple”

    → FALSE = does not contain

  • FILTER(...)

    Returns only the rows where the result is TRUE

    → If no matches are found, it returns "No Match" instead of an error


 Example Use Cases

This method is useful when working with:

  • Product names

  • Customer feedback or comments

  • Categories or tags

  • Any messy text data


Common Mistakes

  1. Confusing SEARCH vs FIND

    • SEARCH → not case-sensitive (recommended)

    • FIND → case-sensitive

  2. Mismatched ranges

    • Make sure your ranges or table columns are aligned (same number of rows).


Pro Tip

This formula uses a structured table (Keyword_Filter), which makes it easier to read, maintain, and scale as your dataset grows.


Download the Practice File

You can download the sample Excel file here and try it yourself:


Final Thoughts

If you’re still manually filtering or using Ctrl + F…

This method is faster, dynamic, and works even with messy data.


 
 
 

Comments

bottom of page