Range contains one of many substrings

An excel formula to range contains one of many substrings

Related Functions

Sponsored Link

 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:



Sponsored Link

0 votes. 0 / 5