Range contains one of many substrings

Range contains one of many substrings


If you need to test a range to determine if contains one of many substrings (partial matches, specific text, etc.) you can use use a formula that uses the SEARCH function, with help from the ISNUMBER, and wrapped in SUMPRODUCT.

How the formula works

For each substring you are looking for…

When a substring is found, the SEARCH function returns the position of a substring in text. If no match is found, SEARCH returns the #VALUE! error.

The ISNUMBER function returns TRUE for numbers and FALSE for anything else. So, if SEARCH finds a substring, it returns the position as a number, and ISNUMBER returns TRUE. When SEARCH returns the #VALUE! error, ISNUMBER returns FALSE.

Because both rng and substrings have multiple values, the result from SEARCH and ISNUMBER will be multiple values — an array of TRUE / FALSE values. The double negative forces the TRUE / FALSE values to 1 / 0 respectively, and SUMPRODUCT adds up the total result. Any positive number means that the formula found at least one substring in rng.

Case-sensitive option

SEARCH is not a case-sensitive function. If you need to check case as well, replace SEARCH with the FIND function like so:



1 votes. 4.5 / 5

Excel - Excel Functions - Excel Formulas