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
=SUMPRODUCT(--ISNUMBER(SEARCH(substrings,rng)))>0

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:

 
=SUMPRODUCT(--ISNUMBER(FIND(substrings,rng)))>0

 

Sponsored Link

0 votes. 0 / 5