filtering

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel Training and help » Filtering

Filtering

resolvedResolved · Urgent Priority · Version 2003

Katie has attended:
No courses

Filtering

I have cells that contain text in each cell of a column. I need to filter selected cells that contain certain parts of text, however this does not seem to be working for me. I have tried auto filter but it seems to display everything even when I click from the drop down list, what certain parts of text I want that are within the cells, does not display on their own.

Please help

Many thanks

RE: Filtering

Hi Katie

Thank you for your question

If you switch the autofilter on, click on the filter on the column that you wish to filter and then choose custom autofilter from the list

If you click on the combo box containing the word equals, you can then change this to "contains"

Using this, you will be able to filter on parts of the field.

Hope this is useful

Regards

Stephen

RE: Filtering

Hi Stephen

I had tried this, but because the words within the cells I want to display are all very similar, it shows all of the cells up.

I want certain cells that include commencement of any work, commencement of work and shall be submitted to appear as filtered cells that contain these phrases. The filter does not seem to filter them as separate things.

Does the filtering only pick up on all of words and not selected text in the cells?

Many thanks

RE: Filtering

Hi Katie

Thanks for your reply

Can you just confirm my understanding? Do the phrases "commencement of any work", "commencement of work" and "shall be submitted", appear in the same column and you want the filter to select on anyone of them?

Thanks

Stephen

RE: Filtering

Hi Stephen

These phrases are within a string of text in each cell of a column. Id like to filter the cells where each of these phrases crops up within the strings of text in the cells of the column if this is possible?

Many thanks

RE: Filtering

Hi Katie

You need to use an advanced filter to accomplish this. Here is what you have to do

First find an unused column. In the first row type in the title of the column that you want to filter on. Then underneath this type in each of the criteria in successive rows preceding and succeeding each with a * symbol. For example

* commencement of work *.

Then select a cell in the data that is to be filtered and click on data-filter-advanced filter.

Make sure that the range to be filtered is correctly identified. In the criteria range enter the cells that contain the column title and the three criteria.

Then click OK

I have enclosed a sample worksheet that illustrates this.

Hope this is OK

Regards

Stephen

Attached files...

Advanced filters.xls

RE: Filtering

Hi Stephen

Thanks very much for this, however when I come to selecting a cell, data, filter advance, I receive an error message saying: Microsoft office cannot determine which row in your list or selection contains column labels, which are required for this command etc..

Please could you advise?

Much appreciated

RE: Filtering

Hi Katie

Every Column in your list needs to have a heading, otherwise Excel cannot identify it as a list. In addition, there should be no blank rows between the heading row and the first row of data.

If this doesn't help, I suggest you email me the spreadsheet at stephenATstl-training.co.uk and I'll have a look at it for you

Regards

Stephen

 

Training courses

 

Training information:

See also:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Generate randon numbers

Some types of analysis require you to use randomly generated numbers. You can also use randomly generated numbers to quickly populate an Excel spreadsheet. There's an easy function you can use to do this automatically. Here are a few of the ways you can use it:

Type =RAND() in a cell to generate a number between 0 and 1.
Type =RAND()*100 to generate a number between 1 and 100.

View all Excel hints and tips


Server loaded in 0.1 secs.