Excel allows its users to find out if the cell contains some words but not others. It is equipped with functions that enable a user to validate the presence of a specified word in a cell. It also helps it in validating that it does not contain the non-specific words. A user is required to enter the equation into a corresponding cell or formula bar manually. It should be careful of the logical values, signs, and placement of the variables for obtaining a feasible outcome. This tutorial will highlight the method that anyone can use for finding out if the cell contains some words but not others on Excel.
Example:
Figure1. Example on how to find if a cell contains some words but not others
Formula:
=AND(COUNT(SEARCH(inc,A1))>0,COUNT(SEARCH(exc,A1))=0)
Explanation:
A user is often assigned with the task to check if a selected cell contains specified words but, it should not include the words which are not mentioned in the text string. Next, a user should use an array formula for activating the variables and functions incorporated into the equation. It generally comprises of SEARCH and COUNT functions, which is further strengthened by the integration of AND function to the equation.
How does the formula work?
We have used the following equation in Figure1:
=AND(COUNT(SEARCH(E6,B5))>0,COUNT(SEARCH(G5,B5))=0)
Here we have used a combination of AND, COUNT, and SEARCH functions collectively. Also, we have specified if the equation meets specific criteria selected by a user. The formula is placed in C5, and it would automatically generate TRUE if it contains any of the words mentioned in Column E (included). If not, then, it would return FALSE to confirm that the cell does not contain a stipulated word in the cell.
Figure2. Example on how to find if a cell contains some words but not others
When using this formula, you should use Control + Shift +Enter (CSE) for enclosing the array formula. If you are unable to retrieve the value then, you should look for {} at the beginning and end of the formula.
Leave a Comment