Filter data if cell contains specific text

Filter data if cell contains specific text

To filter data if a cell contains specific text in Excel, you can use a formula based on the FILTER, ISNUMBER, and SEARCH functions.

How to filter data containing specific text?

Here are the steps to filter data if a cell contains specific text in Excel:

  1. Select cell D3.
  2. Type =FILTER(range, ISNUMBER(SEARCH(“text”, range)), “”), where range is the cell range you want to filter, and “text” is the specific text to search for.
  3. Press Enter.
  4. The formula will return rows where cells in the specified range contain the specific text.

Example

In the example, we have a dataset in range B3:B11 containing a list of state names. The goal is to filter and display only the names that contain the substring “al.” The formula in cell D3 uses the FILTER function combined with the SEARCH and ISNUMBER functions to identify and extract the relevant entries.

Apply the following formula:

=FILTER(B3:B11, ISNUMBER(SEARCH(“al”, B3:B11)), “”)

Filter data if cell contains specific text

The result is a dynamic list of states from the “Range” column that include the specified text pattern “al.”

Explanation

Here’s a detailed breakdown of how the formula works:

SEARCH(“al”, B3:B11)

The SEARCH function looks for the substring “al” in each cell within the range B3:B11. SEARCH returns a number representing the position of the first occurrence of “al” in each cell. If “al” is found, the position (a positive number) is returned, indicating where the substring starts within the text. If “al” is not found in a cell, SEARCH returns a #VALUE! error instead.

The SEARCH function looks for the substring

For the cell B3 containing “Alabama”, SEARCH(“al”, “Alabama”) returns 1 (the starting position of “al”). For B7 containing “Florida”, SEARCH(“al”, “Florida”) returns #VALUE! since “al” is not present.

ISNUMBER(SEARCH(“al”, B3:B11))

The ISNUMBER function checks if each result from SEARCH is a number. ISNUMBER returns TRUE if SEARCH finds “al” (indicating that the position is a number) and FALSE if SEARCH returns an error (#VALUE!). The expression returns an array of TRUE or FALSE values for each cell in B3:B11. TRUE means the cell contains “al” and FALSE means it does not.

The ISNUMBER function checks if each result from SEARCH is a number

For “Alabama”, ISNUMBER(SEARCH(“al”, “Alabama”)) returns TRUE. For “Florida”, ISNUMBER(SEARCH(“al”, “Florida”)) returns FALSE.

FILTER(B3:B11, ISNUMBER(SEARCH(“al”, B3:B11)), “”)

FILTER uses the ISNUMBER(SEARCH(“al”, B3:B11)) array to decide which rows to include. Only cells where ISNUMBER returned TRUE (cells containing “al”) are included in the results. If none of the cells contain “al”, FILTER returns an empty string (“”) as specified by the third argument.

Based on the example, the cells “Alabama”, “California”, and “Alaska” contain “al”, so FILTER returns only these values.

Download the practice file!