Filter text contains

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)))

Note: FIND is case-sensitive, but does not support wildcards.