×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Filtering for words within cells
2

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?

Read the Eng-Tips Site Policies at FAQ731-376  

RE: Filtering for words within cells

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.

RE: Filtering for words within cells

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.

RE: Filtering for words within cells

(OP)
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  

RE: Filtering for words within cells

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.

RE: Filtering for words within cells

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

RE: Filtering for words within cells

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.

RE: Filtering for words within cells

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.

RE: Filtering for words within cells

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.

RE: Filtering for words within cells

(OP)
Yep, Thanks too!

Read the Eng-Tips Site Policies at FAQ731-376  

RE: Filtering for words within cells

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close