Go Back

Cell contains some words but not others

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.


Figure1. Example on how to find if a cell contains some words but not others




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:


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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need to combine formulas and show some columns but not others
Solved by S. Y. in 29 mins
When I right click a cell that contains a comment the menu does not contain a choice to show/hide. This only occurs in some of my worksheets. Others work fine. I use Excel 2010.
Solved by E. D. in 14 mins
I have just started to use Draw, it is available on some tabs but not on others
Solved by D. Y. in 18 mins

Leave a Comment