To filter data to include data based on a “contains specific text” logic, you can use the FILTER function with help from the ISNUMBER function and SEARCH function. In the example shown, the formula in F5 is:

=FILTER(B5:D14,ISNUMBER(SEARCH("rd",B5:B14)),"No results")

Which retrieves data where the street column contains “rd”.

How this formula works

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D14, which contains the full set of data without headers. The include argument is based on a logical test based on the ISNUMBER and SEARCH functions:

ISNUMBER(SEARCH("rd",B5:B14))

In brief, the SEARCH function is set up to look for the text “rd” inside the street data in B5:B14. Because this range includes 10 cells, 10 results are returned. Each result is either a number (text found) or a #VALUE error (text not found):

{#VALUE!;11;#VALUE!;#VALUE!;13;#VALUE!;#VALUE!;18;17;#VALUE!}

And the resulting array returned to the FILTER function as the “include” argument:

{FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;TRUE;FALSE}

This array is used by the FILTER function to retrieve matching data. Only rows where the result is TRUE make it into the final output.

Finally, the “if_empty” argument is set to “No results” in case no matching data is found.

Wildcards

The SEARCH function supports wildcards, so the filter logic can include these characters.

Case-sensitive

For a partial match, case-sensitive filter, you can adjust the formula to use the FIND function instead of SEARCH like this:

=FILTER(rng1,ISNUMBER(FIND("TXT",rng2)))