98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel Training and help » Filtering
Filtering
Resolved · 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...
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 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
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Generate randon numbersSome 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: |