![Trending Articles on Technical and Non Technical topics](/images/trending_categories.jpeg)
Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to Check If Cell Contains One of Several Values in Excel?
If we have a dataset in which we need to check if there are any cell values or text which is not required or inappropriate, then the same can be identified in seconds using an excel function. There are many functions through which we can identify specific set of text in cell values of the complete dataset. Let’s have a look at the formulas and example and learn how to implement the same in a dataset.
Check if dataset contains one or more values from a specific list
Check if dataset contains one or more values from a specific list
Step 1 − A sample data has been created as shown below.
Step 2 − Enter the following formula in a separate column, where you want the result to appear.
=SUMPRODUCT(--ISNUMBER(SEARCH(find_text_range, within_text))), “Yes”,”No”)
Step 3 − Now drag the cell to copy the formula in other cells also to view the output against all cell
Formula Syntax Description
Argument | Description |
---|---|
SEARCH (find_text, within_text, [start_num]) |
|
--ISNUMBER(value) |
|
SUMPRODUCT(expression, range) |
|
IF(logical_test, {value_if_true},{value_if_false} |
|
Conclusion
However, there are other combination formulas are also available for doing the same task. Some of the formulas are as following −
=TEXTJOIN(", ", TRUE, IF(COUNTIF(<where to find cell>, "*"&<What to find range>&"*"), <What to find range>, ""))
This formula is applicable for excel 2019 and above version. Though there are many methods to search specific text in the dataset but the one explained in this article is applicable to all versions of excel. Keep learning excel and keep practicing.