Filter text contains

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:
Which retrieves data where the street column contains "rd".
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:
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:
Note: FIND is case-sensitive, but does not support wildcards.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.