Filtering for words within cells 2

[ColourfulFigsnDiags]

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?

Read the Eng-Tips Site Policies at FAQ731-376

 

[electricpete]

Let's say original data is in row A starting at A2

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.

 

[electricpete]

Oops. I used A1 and B2... should have been on same row. You know what I meant.

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.

 

[ColourfulFigsnDiags]

Fantastic and elegant solution electricpete, amazed I didn't think of that myself!

Thank you very much!

Read the Eng-Tips Site Policies at FAQ731-376

 

[ab123456]

It looks like you already have an answer but if you want to filter in place the advanced filter feature in excel can do exactly what you are looking for.

 

[pmover]

i suppose i'm a little confused.

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

 

[electricpete]

With autofilter you click the cell at top of the column and it gives you choices of values to select for filtering. I don't see anything like "contains" there.

Are you maybe describing a feature of advanced filtering?



=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Aha. Now I see what you mean. There is a choice "Custom" which pulls up a window that can be used for contains.

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.

 

[electricpete]

Also it looks like you can combine criteria in that window. Good stuff. A star for you.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[ColourfulFigsnDiags]

Yep, Thanks too!

Read the Eng-Tips Site Policies at FAQ731-376

 

[pmover]

whew! thanks electricpete! momentarily thought i was really confused until i read all postings.

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