Text filters

[elchenuk]

Hi I'm fairly new to Excel and I could do with some advice.

I want to filter a column in Excel based on four words e.g. 'essex', 'bedford', 'manchester' and 'leeds'. However I can only insert no more than 2 text criteria in the text filters -> contains menu.

How do I add more text filters?

thanks

 

[Jonmo1]

Welcome to the board...

Use a helper column.

If you want to filter column A on essex or bedford or manchester or leeds
In B2 put = OR(A2={"essex","bedford","manchester","leeds"})
Then filter on Column B for True

Hope that helps.

 

[elchenuk]

Cheers jonmo1!

failed to mention that some of those words are contained within longer phrases in column A, so for example 'new homes in leeds' etc

I guess it must be a case of just adjusting the query with some sort of 'contains' command?

 

[Jonmo1]

Try

=ISNUMBER(LOOKUP(2^15,SEARCH({"essex","bedford","manchester","leeds"},A2)))

 

[elchenuk]

Brilliant that works perfectly!

cheers

 

[Jonmo1]

Glad to help, thanks for the feedback..

You can also put your list of key words in a range of cells.
Makes for easier adjustment, and easier to hold MANY possible values...

=ISNUMBER(LOOKUP(2^15,SEARCH(F2:F5,A2)))

F2:F5 = essex bedford manchester leeds

 

[elchenuk]

thanks jonmo1 that works even better as I can use this as a template

Incidentally do you know where I can go to find out more about VBA and specifically lookups?

thanks for your help

 

[Jonmo1]

Personally,I'm not much of a book learner..
Almost everything I know about Excel, I've learned right here at MrExcel.com
Keep reading
Ask Questions
Answer questions <--this helps alot

But here are a couple other good resources
http://www.cpearson.com/Excel/MainPage.aspx

http://www.contextures.com/tiptech.html