Excel Formulas: Cell Contains One of Many Things with Exclusion!!

So far, we have learned the formulas to test the cell that contains one of many things in Excel. Likewise, here we will are going to see how to check if the cell contains one of many things with exclusion in Excel. Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Generic Formula:

You can use the below formula to check if the cell contains one of many strings, while excluding others in Excel.

=(SUMPRODUCT(–ISNUMBER(SEARCH(include,A1)))>0)

*(SUMPRODUCT(–ISNUMBER(SEARCH(exclude,A1)))=0)

Syntax Explanations:

SUMPRODUCT – This function helps to multiply the corresponding arrays or ranges and returns the sum of the products. Read more on the SUMPRODUCT Function.
ISNUMBER – The ISNUMBER Function returns TRUE when a cell contains a number, and FALSE if there will not be a number.
SEARCH – In Excel, the SEARCH function locates the character between two text strings and returns to the number of the starting position of the first text string from the first character of the second text string.
A1 – It represents the input text strings.
Include – It specifies the criteria that help to check the cell value.
Exclude – It represents the excluding values.
Minus Operator (-) – This symbol will help to subtract any two values.
Comma symbol (,) – It is a separator that helps to separate a list of values.
Parenthesis () – The main purpose of this symbol is to group the elements.

Practical Example:

Let’s consider the below example image.

Here, we will enter the input text strings in Column B.
Now, we need to check if the cell contains one of many things while excluding others in Column G.

Select any cell and apply the above-given formula.

Finally, press ENTER to get the result out.

Verdict:

In this guide, you can get some to know the formulas to test if the cell contains one of many things with exclusion in Excel. If you have any questions , feel free to comment.

Thank you so much for visiting *Geek Excel!! **If you want to learn more helpful formulas, check out Excel Formulas *!! **