Filtering for words within cells
Filtering for words within cells
(OP)
Hi all!
I've got a database which lists (wait for it) the contents of boxes. Each row contains the details of a particular file within a particular box. Associated with each file are key-words, which are stored in a cell. For example, one of the keyword columns is for what chemicals the particular file deals with, so the cell might contain, "Ammonia, air, nitric acid".
I would like to be able to display all files which contain the key word "Ammonia". Unfortunately, because the keyword appears in a cell with other key words, I cannot use the Auto filter to do this.
Does anyone know of an easy method to do this?
I've got a database which lists (wait for it) the contents of boxes. Each row contains the details of a particular file within a particular box. Associated with each file are key-words, which are stored in a cell. For example, one of the keyword columns is for what chemicals the particular file deals with, so the cell might contain, "Ammonia, air, nitric acid".
I would like to be able to display all files which contain the key word "Ammonia". Unfortunately, because the keyword appears in a cell with other key words, I cannot use the Auto filter to do this.
Does anyone know of an easy method to do this?
Read the Eng-Tips Site Policies at FAQ731-376





RE: Filtering for words within cells
Create a new column B which will be true if keyword is included and false if note.
Put the following equation into B2 that column
=+NOT(ISERR(FIND("SEARCHTEXT",A1)))
where SEARCHTEXT is what you're looking for. Copy the formula all the way down column B. Now conduct auto-filter on column B.
Note you can easily get tripped up by case differences. The following code would make it case insensitive
=+NOT(ISERR(FIND(UPPER("SEARCHTEXT"),UPPER(A1))))
Also if you find yourself doing this operation often you can make it a little easier by create a named cell in another sheet which contains the text you're going to search for. Then put the name of that cell into the formula in column B instead of te actual search text.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Filtering for words within cells
Also I forgot to say that the find() function returns an error if it can't find the search text. The iserr() function tests for the presence of an error. Final result is TRUE if search text is present and FALSE if not. Autofilter to find the TRUE results.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Filtering for words within cells
Thank you very much!
Read the Eng-Tips Site Policies at FAQ731-376
RE: Filtering for words within cells
RE: Filtering for words within cells
it seems that the autofilter will work if the choice selected is cell "contains", + searchable text.
a useful solution is provided, but i suppose i do not understand why autofilter will not work.
-pmover
RE: Filtering for words within cells
Are you maybe describing a feature of advanced filtering?
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Filtering for words within cells
That's very useful. A much better solution. I'm glad you mentioned it because that will be handy for me as well.
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Filtering for words within cells
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Filtering for words within cells
Read the Eng-Tips Site Policies at FAQ731-376
RE: Filtering for words within cells
yes, autofilter does have multiple search criteria capabilities (only two though). Note that wildcards can be used as well - see bottom left portion of dialog box - for a single or multiple character choice.
you are welcome and thanks!
-pmover