In Microsoft Excel Spreadsheet or google sheets, when cells contain multiple strings, how can we accomplish this task if you want to check whether these cells contain more than one given string and exclude other given strings?
In this article, we will discuss how to check if a cell contains more than one given string and exclude other given strings by using a formula. You can create a new nested formula by using the SEARCH function (google sheets or Excel), the ISNUMBER function and the SUMPRODUCT function.
The generic formulas are as follows.
= ( SUMPRODUCT ( -- ISNUMBER (SEARCH(INCLUDE_STRINGS, TEXT_STRING))) > 0 ) * ( SUMPRODUCT ( -- ISNUMBER (SEARCH(EXCLUDE_STRINGS, TEXT_STRING))) = 0 )
Table of Contents
Check Cell If Contains One of Many with Exclusions in Google Sheets & Excel
Suppose you have two data tables A3:B12, E1:F4, you want to check whether the cell area A3:A12 contains one of the values in the cell range E2:E4, but does not contain any of the values in the cell range F2:F3. You can build the following google sheets or Excel formulas based on the above general formula.
=(SUMPRODUCT(--ISNUMBER(SEARCH(INCLUDE_STRINGS,A4)))>0) * (SUMPRODUCT(--ISNUMBER(SEARCH(EXCLUDE_STRINGS,A4)))=0)
where INCLUDE_STRINGS is the named range E2:E4, and EXCLUDE_STRINGS is the named range F2:F3.
You can enter the above formula in cell B4 and press Enter to apply the formula.
Let’s see how this formula works：
= ( SUMPRODUCT ( — ISNUMBER (SEARCH(INCLUDE_STRINGS, A4))) > 0 )
This formula is used to check whether cell A4 contains INCLUDE_STRINGS range of strings, SEARCH function will find from A4 contains INCLUDE_STRINGS range of strings, if it exists, will return the position of the string in cell A4, otherwise return # VALUE!
ISNUMBER formula will SEARCH function return value into TRUE or FALSE, when the search function returns a number, ISNUMBER function will return TRUE, otherwise it will return FALSE.
The double negative sign before ISNUMBER converts the TRUE or FALSE value returned by the ISNUMBER function to 1 and 0.
=(SUMPRODUCT(–ISNUMBER(SEARCH(EXCLUDE_STRINGS,A4)))=0)
This formula is used to check whether the cell does not contain any of the values in F2:F3. Where the SEARCH function is used to perform a lookup string and return the position of the string in cell A4 or return the #VALUE! error message.
Related Functions
- Excel ISNUMBER function
The Excel ISNUMBER function returns TRUE if the value in a cell is a numeric value, otherwise it will return FALSE.The syntax of the ISNUMBER function is as below:= ISNUMBER (value)… - Excel SEARCH function
The Excel SEARCH function returns the number of the starting location of a substring in a text string.The syntax of the SEARCH function is as below:= SEARCH (find_text, within_text,[start_num])… - Google Sheets SUMPRODUCT function
The Google Sheets SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…