Excel Formulas to Test if the Cell Contains all of Many Things ~ Easy Tips!!

Let’s say you have a list of text strings in one range, and you want to find out if these cells contain all the words in another range. In this chapter, we will help you test if the cell contains all of many things 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:

If you want to check if the cell contains all the listed items, you can use the below formula.

*=SUMPRODUCT(–ISNUMBER(SEARCH(things,A1)))=COUNTA(things) *

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.

COUNTA – It is used to counts the number of cells that are non-empty and returns the output in numbers. Read more on the COUNTA Function.

A1 – It represents the input text strings.

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.

First, we will enter the input text strings in Column B.

Here, we need to test if the cell contains all the items listed in Column E.

Select any cell and apply the above-given formula.

Finally, press ENTER to get the result out.

Conclusion:

Hope you like this article on how to check if the cell contains all of many things in Excel. I hope that this article is helpful. Please share your query below in the comment box. We will assist you.

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

Read Also:

How to Find the Last Column of Data in Excel Office 365?

Excel Formulas to Capitalize the First Letter of a Text String!!

Excel Formulas to Add a Line Break Based on the Current OS ~ Quickly!!

How to Wrap Text Manually in Excel Office 365? {3 Ways}