EXCEL - Filter(Contains) Function. Filter your table with just part of the description

[@izzybizzybee72]

Really useful, using it to filter consultation responses!

[@datauntelligence]

Yeah, I agree. I use this formula all the time. Thanks for the comment, it's great to see that it has helped you out.

[@traciesmobile683]

I have been using this formula for a couple of years. You can use boolean as well with it to search for criteria from more than one column. It is great for file indicies and document control. Also if you name your table, makes the formula tidier.

[@datauntelligence]

Great advice. Always good to hear from my fellow Excel enthusiasts :)

[@izzybizzybee72]

A formula to highlight the text searched for would also be useful! 🙂

[@datauntelligence]

Ah ha, yes. That would be good. You must use spreadsheets a lot?! I would do this by adding in some 'conditional formatting' based on another cell. You can format cells (shade/color) based on another cell, or a reference cell. This video shows shows you how to do it based on numbers, but you can change it to reference a cell with text. Maybe it will give you some ideas. kzbin.info/www/bejne/q4jacpV8mZeAqZo

[@SergSolod]

Thank you! Well done, surely this formula is very useful.

[@datauntelligence]

Hi. I definitely agree. I use this formula all the time now. Thanks for the comment!

[@jillianonthehudson1739]

That's ingenious, thanks!

[@datauntelligence]

Hey, thanks for the comment. Glad you enjoyed. I love how Excel allows you to mix and match formula to get the solution you need.

[@simplelife.2012]

Hello

[@izzybizzybee72]

My latest conundrum. How to do this across multiple worksheets? I know it involves using VSTACK, but not sure how to smash them together??

[@datauntelligence]

Hey hey. Take a look at this video, that shows you how to filter across multiple sheets. You could use the contains formula as part of this. kzbin.info/www/bejne/d6aQm2OvqaqkfJY

[@izzybizzybee72]

@@datauntelligence Thanks, I did look at that video, but I can't get the two to work together. This is what I tried: =FILTER(VSTACK(sheet1:Sheet2!A4:E200),(VSTACK(sheet1:Sheet2!E4:E200)""))*(VSTACK(sheet1:Sheet2!A4:E200,ISNUMBER(SEARCH(E1,E4:E300)),"No entries")) OR =FILTER(VSTACK(sheet1:Sheet2!A4:E200),(VSTACK(sheet1:Sheet2!E4:E200)""))*(A4:E200,ISNUMBER(SEARCH(E1,E4:E300)),"No entries"). But neither worked!

[@cherimaeeee19]

does it work on Google Sheets? it had result on "ERROR, FILTER has mismatched range sizes. Expected row count: 998. column count: 1. Actual row count: 1, column count: 1."

[@arik541]

please tell me what am I doing wrong? the formula gives an error, I want to do the same thing, but with all the sheets =FILTER(FILTER(VSTACK('122023'!A2:AA4958),VSTACK('122023'!A2:A4958)0),ISNUMBER(SEARCH(AD1,VSTACK('122023'!B2:B4958))))

[@sner323]

SUPPER HELPFULL

[@datauntelligence]

Great, thanks for the comment.

[@amitkhadse7087]

nice but filter is not showing in my excel, after = only filterxml is coming any idea how to avail filter command

[@datauntelligence]

Hi there, this function is only available on excel 365 and 2021 version. That might be causing the issue.

[@deen9oh9]

THANKS !!!

[@datauntelligence]

No probs. I'm happy you liked it.

[@arik541]

Super Thanks

[@datauntelligence]

No worries. Thanks for the comment

[@xalidxelilov1827]

why FILTER formula is not working on my excell?

[@blackiceummair]

If you are using an older version of excel then u will not be able to view the filter formula. It work on the recent versions only

[@datauntelligence]

@blackiceummair thanks... that could be causing the issue

[@noraaplor8366]

poor me no filter function in my laptop

[@datauntelligence]

Argh... that's frustrating.