Excel FILTER Function with Partial Text Criteria or Contains Criteria. Extracting Records. EMT 1653

Learn how to extract records from a table based on Partial Text Criteria or Contains Criteria.
1. (00:01) Introduction
2. (00:01) Extracting Sales Records Based on Partial Product Name.
3. (00:27) FILTER, SEARCH and ISNUMBER Functions to extract records Based on Contains Condition. Formula Not Case-Sensitive.
4. (02:29) FILTER, FIND and ISNUMBER Functions to extract records Based on Contains Condition. Formula Is Case-Sensitive.
5. (02:59) Extract Customers on Particular Street Example.
6. (03:11) Summary and End Video Links

[excelisfun]

Topics:
1. (00:01) Introduction
2. (00:01) Extracting Sales Records Based on Partial Product Name.
3. (00:27) FILTER, SEARCH and ISNUMBER Functions to extract records Based on Contains Condition. Formula Not Case-Sensitive.
4. (02:29) FILTER, FIND and ISNUMBER Functions to extract records Based on Contains Condition. Formula Is Case-Sensitive.
5. (02:59) Extract Customers on Particular Street Example.
6. (03:11) Summary and End Video Links

[sasavienne]

This FILTER formula is simply the best of all the Dynamic Array Formulas. Thanks Mike. The trick of ISNUMBER is excellent.

[richardhay]

Somehow I missed this video when you created it!?! Just stumbled into it. This an important topic with FILTER especia)y since ADV FILTER permits partial text filtering and users want FILTER to do the work of ADV FILTER. Search is useful here because it accepts WULDCARDS which can help further refine partial text criteria. Sorry for being late for class!!LOL.

[jimfitch]

I’m using these a lot these days. They’re things of beauty. Thanks, Mike

[rajeshghai]

Sir, You are great GURU (Teacher) for me. Thanks so much.

[nourreldeenmohamed]

thank you for all thing you are doing to us ❤❤

[FobbingScullion]

Simply a perfect solution! I'm sure you've had ways of doing this with Index and Match previously, but this is quite an elegant formula. Thank you for sharing it!

[hoiyinwan]

Thank you for your videos, amazing as always!! ❤️💕❤️

[paulsingleton]

Another excellent video Mike and the bonus tip to use the Find function, thank you. 👏

[GeertDelmulle]

Great trick: the Search (or Find) isnumber combo is the “new” ‘Contains’ filter condition that we know from PQ-M.
Thanks for the refresher video on this. :-)

[EduardoBicelisGarcia]

Your classes are smart + you give access to files where to learn&practice THANKS.

[mattschoular]

I love it. Thanks Mike More reasons why ExcelIsFun.

[spilledgraphics]

Mike!, I must tell you, I am still not getting my hands dirty
with Dynamics Arrays, but I promise I will be soon and
I know my favorite channel to go to!!! Go Team!!!

[Fxingenieria]

Thank you very much for this excellent video, just what I was looking for !!

[anthonyverdin]

Mike!!!! I wish I could mash that like button 100 time, this is EXACTLY what I need!!!

[anthonyverdin]

Mike!!! I wish I could mash the like butto 100 times... Your video will definitely help me with having to manually search through a list.

[lpanades]

Thanks. I did the same with the criteria on A1 an auxiliar collumn (D) with the fórmula Cont.if(A3;"*"?&$A$1&"*"); them I check in filter function if that collumn is 1. But, it is not a finer solution. Sorry, really your solution is by far much much much much better. Thanks again.

[kjsutherland]

Hello Mike, great video and makes a lot of sense, for some reason the formula is returning a #Value! error- any idea why this might be ? I have checked the spelling and I am spelling my criteria text to be searched. I am using the office 365 web excel version though- not sure if that matters.

[ismailismaili]

Man these things are mind blowing I really respect you for sharing the info Mr. Mike thank you so much your videos are really appreciated.

[chrism]

That is awesome Mike, Office 365 is the best