Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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.

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.

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

Another blog reader asked this question today on Excelchat:
Solution examples
Yes hello Im looking for help on conditional formatting I currently have a conditional format for D9>D32 and so on from D9:S20. I need to add an if statement that references cell D4 if the first four characters are 1358 for the rule to apply can you please assist?
Solved by Z. U. in 20 mins
I have a list or people who are ranked in numbers from 3 to 6 I need to recognize the contents of each multiple cells and create an equivalent letter. for that value into another column. e.g. Cell E1 = 3 to show in new cell that row (H1) the letter "C" I have multiple rows with different values in column "E" Can this be done?
Solved by I. J. in 30 mins
Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins
If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins
I am trying to make a chart that turns a week range red if nothing is entered in the range. If something is entered then I would like it to turn green. Please Help
Solved by E. U. in 43 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

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

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc