how can I search based on the ending of words? *end doesn't work because it finds "end" anywhere in the word, and ?end finds it anywhere as long as it's not at the beginning because it requires at least one preceding character. Neither option specifies anything about what happen if there is no wildcard after the string being searched.
Wonderful--> And if you want to Exclude the Searched Text from the Filtered list then you can also wrap the ISNUMBER function with NOT... and it gives you a Filtered result after eliminating the Searched Text ...
Thanks for the comment. You're spot on. You can wrap inside a NOT function whatever you enter in the second parameter to filter out the found set as opposed to showing the found set.
Thank you for the comment. It is possible to filter data based on multiple items. Here's a video showing one way to do this, ua-cam.com/video/A5N3p408tF0/v-deo.html. Hope this helps.
Thanks for the question. To use a column instead of a range, you just need to replace the specific cell reference with a reference to the entire column. For example, if we make this change to the formula in the video, we get: =FILTER(E:F,ISNUMBER(SEARCH($C$1,E:E)),"-") This equation will filter all of columns E and F wherever a value in column E meets the criteria set in the first parameter of the SEARCH function. I hope this helps.
I'm glad you found the video helpful, and thanks for the question. To answer your question, let's use the equation at timestamp 5:41 in the video as our example: =FILTER($E$4:$F$8,ISNUMBER(SEARCH("XYZ",E4:E8)),"-") This equation returns all Serial Numbers that contain "XYZ". If we want the opposite of this, in other words, the Serial Numbers that do not contain "XYZ", we can wrap the ISNUMBER() portion of the equation inside a NOT() function: =FILTER($E$4:$F$8,NOT(ISNUMBER(SEARCH("XYZ",E4:E8))),"-") The NOT() function flips the TRUEs and FALSEs in the ISNUMBER() function, meaning we get the opposite of what is asked for in the SEARCH() function. I hope this helps.
I'm getting a #NAME? error when my source table contains a blank. Is there any way around this? I usually use an "IF" to return a blank, but this isn't working.
Thanks for the question. The #NAME error occurs when there is a typo in one of the function names. Before adding error handling, I would double check to make sure the functions are spelled correctly. For reference, the functions used in the equation in the video are FILTER, ISNUMBER, and SEARCH. It’s not recommended, but if you absolutely need to mask the error, you can use the IFERROR function. This would look like: =IFERROR(equation,""). IFERROR returns the value of the equation if it doesn't produce an error. If it does produce an error, it returns the second argument which, in this example, is an empty string. I hope this helps.
Thanks for the comment. To match “ban” at the beginning of the word, assuming your word is in cell A1, you can use the equation “=SEARCH(“ban”,A1)=1”. This will return TRUE for “banana” since “ban” occurs at position 1 of “banana”. It will return FALSE for “pearbanana” since “ban” occurs at position 5. The SEARCH function returns the location of the value in the first parameter if it’s found anywhere inside the second parameter. The equation in this video wraps the SEARCH function inside ISNUMBER to return TRUE if the value in the first parameter of SEARCH exists anywhere in the text being searched.
@@ExcelSmith thank you. Have you done multiple column filter formula? I have forename and surname. I have blank forenames. When I do an AND statement I lose all the records where the forename is blank. All the examples I have seen don't seem do deal with blanks
@@prajac Thanks for the question. Here’s a video that shows using multiple criteria with the FILTER function, ua-cam.com/video/A5N3p408tF0/v-deo.html. To have the FILTER function return rows with a blank value in the forename column, you can use the ISBLANK function in the second parameter of FILTER. For example, assuming forename is in column A and surname is in column B, you could use: =FILTER(A2:B4,(ISBLANK(A2:A4))*(B2:B4="Smith”),”-“) The above equation will return matches for any name pairs whose forename is blank and surname is equal to “Smith”. Hope this helps.
@@ExcelSmith Hey, Thank you for the fantastic videos!! May I ask you a question regarding FILTERing with wildcards? I'm really grinding my brain on this the past days; just can't wrap my head around it. The real question would be why the super helpful FILTER functionality falls a bit short when it comes to wildcards, but for now: How can I point to a cell that contains letters or numbers and tell the filter: find the cells where words begin(!) with the letters in this cell (A4 for example)? I know it is basically the same question as in the banana/pearbanana-question from prajac, but I just cannot fit the =1 in so that it works. Might be an issue with referencing a cell in my example. My current formula is =FILTER(‘Tab’1!A:D,ISNUMBER(SEARCH(A4,’Tab1’!A:A,1)),““) - where A4 contains the letters i want to search with. It works nicely for the most part. But now if A4 contains (the letters) JM, the filter also returns rows where the cells in A:A start with FJM. (Modifying the JM or going "around the corner" an extra turn by referencing an additional cell that adds * or ? is not really an option since this would sacrifice some ergonomics and automation regarding other aspects of the spreadsheet. The letters in my cell A4 are already mirrored from another place) Is it possible to get this working or did I just explore the outer boundaries of FILTER? Haha. Any help would be hugely appreciated. Thank you for reading this and keep up the good work! one more thing just in case: my example formula might not be copy-paste-able because I had to mess around with the ‘ as these wouldn't display correctly.
@@hundeparadies7691 Thanks for the question. As I understand your scenario, you want to filter the values in ‘Tab’!A:D where the values in ‘Tab1!’A:A start with “JM” (eg: “JMA”). However, the formula you’re using is also showing rows that contain “JM” like “FJM”. Assuming the values you want in column A always start with “JM”, you could use this FILTER function: =FILTER(‘Tab’!A:D,IFERROR(SEARCH(A4,‘Tab’!A:A,1)=1,FALSE),"") The SEARCH portion of the equation will return TRUE for any values in column A that start with “JM” (eg: “JMA”), FALSE for any values that contain “JM” but that don’t start with it (eg: “FJM”), or a #VALUE! error for any values that don’t contain “JM” anywhere (eg: “ABC”). To ensure the SEARCH function always returns TRUE or FALSE, which is needed by the FILTER function, we can wrap it inside an IFERROR function, setting the second parameter of IFERROR to FALSE so that this is returned when “JM” is not found” I hope this helps. Thanks.
I'm going insane trying to find a solution to my problem. I need to do exactly what you did in this video when searching for text that contains asterisk characters already. However, the results are not what I need. For example, if my search box contains the value AB*12*, I want it to return the results of ABC123 and ABC124 but it's also returning a result of ZABC123 even though there's no asterisk at the beginning. I understand the search function searches "within the range" but is there anyway to make it only search where the asterisks are and disregard any results that have values at the beginning or where there isn't an asterisk?
Thanks for the question. For Excel to include the wildcard characters (“*” and “?”) in the search, we have to escape them by using a tilde (“~”) in front of them. For example, If you want to search for everything that contains “AB*”, you would enter “AB~**” as the first parameter of SEARCH. For example, assume the range A2: A5 contains the following values: “AB*12*”; “AB12”; “ZABC123”; “ZAB*C123*”. If we want to filter all cells that contain “AB*” we could use this equation: =FILTER(A2:A5,ISNUMBER(SEARCH("AB~**",A2:A5)),"") => returns “AB*12*”; “ZAB*C123*” If you want to filter only the row(s) that start with “AB*” you can use this equation: =FILTER(A2:A5,IFERROR(SEARCH("AB~**",A2:A5)=1,FALSE),"") => returns “AB*12*” The important things is that you place a tilde (“~”) before any asterisks or question marks that you want to include in your wildcard search. I hope this helps.
@@ExcelSmith Thank you so much for your reply. I want to clarify. My lookup value in the cell contains asterisks, which are intended to be wildcards. The thing is, if the cell value is AB*123, I would like it to return ABC123 but it's also returning ZABC123 and I don't want it to do that since there isn't an asterisk before the A. The issue is that the SEARCH function searches for the text, including wildcards, WITHIN the text of the lookup range and will automatically include values that have extra characters before or after your lookup value whether or not there are wildcards at the beginning or end. For example, I only want it to return the ZABC123 value if my lookup value is *AB*123 but not if the lookup value is AB*123.
@@BeugsTheLegend Thanks for the clarification. To make sure I understand, when you are searching with “AB*123”, do all of the values you want to find begin with “AB…”? If so, we can check that the result of the SEARCH function equals 1. For example: =SEARCH("AB*123",A2:A5)=1 The SEARCH function will return 1 for the value “ABC123, and 2 for the value ZABC123. Setting the equation equal to 1 would yield TRUE and FALSE. Next, we would need to replace the ISNUMBER function with the IFERROR function, setting the second parameter to FALSE to account for scenarios that do not contain a match for “AB*123”. This gives us the equation: =IFERROR(SEARCH("AB*123",A2:A5)=1,FALSE) This new equation would then be the second parameter of the FILTER equation: =FILTER(A2:A5,IFERROR(SEARCH("AB*123",A2:A5)=1,FALSE),"") That equation would return “ABC123”, but not “ZABC123”. I hope this helps, but please let me know if this still doesn’t get you what you’re needing. Thanks.
@@ExcelSmith Thank you so much for the reply. I’m excited to give it a shot. I am leaving for vacation for the rest of the week and I may not be able to try until next week but I will try it and let you know. Thank you so much!
@@ExcelSmith I am using access but i will start to learn python. I hate excel and my criticism was about the software not about your video. I never understood excel and why is so hard to do it any simple thing.
Working in Excel is definitely different from working in Access. I too have recently begun learning Python. It looks like a great language, and has the flexibility to do almost anything with data. Best of luck learning the new language.
"Wow, excellent teacher! Thank you, man, it was great. You also taught wildcard scenarios excellently."
Thank you so much! Subscribe button smashed.
This was a very helpful explanation and exactly what I needed. Thank you!
Thanks. I'm glad it helped.
A piece of genius. This has solved a challenge I could not figure out.
I love how your explanation is detailed
Thank you. I appreciate it.
Genious! Thanks for the excellent video and explanation
Thank you. I'm glad it was helpful.
THIS IS VERY USEFUL, I WAS DESPERATELY TRYING TO FINDOUT SOME WAY-OUT. THANKS FOR SHARING
Thanks for the comment. I'm glad the video was helpful.
A very insightful video 📹 👏 Thank u for your hard work 👍
Thanks for the comment. I'm glad the video was helpful. 😀
Awesome work
Thanks. Much appreciated.
Perfect & well explained.
Thank you. I'm glad it was helpful.
Great work and really well explained👍
Thank you. I appreciate it.
how can I search based on the ending of words? *end doesn't work because it finds "end" anywhere in the word, and ?end finds it anywhere as long as it's not at the beginning because it requires at least one preceding character. Neither option specifies anything about what happen if there is no wildcard after the string being searched.
Any suggestion on how to only include unique values? I've tried using the "unique" formula but it doesn't work.
Wonderful--> And if you want to Exclude the Searched Text from the Filtered list then you can also wrap the ISNUMBER function with NOT... and it gives you a Filtered result after eliminating the Searched Text ...
Thanks for the comment. You're spot on. You can wrap inside a NOT function whatever you enter in the second parameter to filter out the found set as opposed to showing the found set.
Smith, its possible instead of filtering one filter item. I used/make a list to filter. Its something it will lookup to a list of item.
Thank you for the comment. It is possible to filter data based on multiple items. Here's a video showing one way to do this, ua-cam.com/video/A5N3p408tF0/v-deo.html. Hope this helps.
excellent video one question how can I use this function when I just select a column instead of specific cell range within specific column ?
Thanks for the question. To use a column instead of a range, you just need to replace the specific cell reference with a reference to the entire column. For example, if we make this change to the formula in the video, we get:
=FILTER(E:F,ISNUMBER(SEARCH($C$1,E:E)),"-")
This equation will filter all of columns E and F wherever a value in column E meets the criteria set in the first parameter of the SEARCH function. I hope this helps.
Great learning! How about if i want to filter does not contain that character that I typed in
I'm glad you found the video helpful, and thanks for the question. To answer your question, let's use the equation at timestamp 5:41 in the video as our example:
=FILTER($E$4:$F$8,ISNUMBER(SEARCH("XYZ",E4:E8)),"-")
This equation returns all Serial Numbers that contain "XYZ". If we want the opposite of this, in other words, the Serial Numbers that do not contain "XYZ", we can wrap the ISNUMBER() portion of the equation inside a NOT() function:
=FILTER($E$4:$F$8,NOT(ISNUMBER(SEARCH("XYZ",E4:E8))),"-")
The NOT() function flips the TRUEs and FALSEs in the ISNUMBER() function, meaning we get the opposite of what is asked for in the SEARCH() function. I hope this helps.
Yes it works.. will try to my file.👌😀
Awesome. Glad it worked for you. 😀
Awesome
Thanks. I’m glad you liked it.
I'm getting a #NAME? error when my source table contains a blank. Is there any way around this? I usually use an "IF" to return a blank, but this isn't working.
Thanks for the question. The #NAME error occurs when there is a typo in one of the function names. Before adding error handling, I would double check to make sure the functions are spelled correctly. For reference, the functions used in the equation in the video are FILTER, ISNUMBER, and SEARCH.
It’s not recommended, but if you absolutely need to mask the error, you can use the IFERROR function. This would look like: =IFERROR(equation,""). IFERROR returns the value of the equation if it doesn't produce an error. If it does produce an error, it returns the second argument which, in this example, is an empty string.
I hope this helps.
Thank you unlimited like continue
Thank you. I appreciate it.
I couldn't get wildcard to work. When I type ban? On banana, pearbanana it brings up both
Thanks for the comment. To match “ban” at the beginning of the word, assuming your word is in cell A1, you can use the equation “=SEARCH(“ban”,A1)=1”. This will return TRUE for “banana” since “ban” occurs at position 1 of “banana”. It will return FALSE for “pearbanana” since “ban” occurs at position 5.
The SEARCH function returns the location of the value in the first parameter if it’s found anywhere inside the second parameter. The equation in this video wraps the SEARCH function inside ISNUMBER to return TRUE if the value in the first parameter of SEARCH exists anywhere in the text being searched.
@@ExcelSmith thank you. Have you done multiple column filter formula? I have forename and surname. I have blank forenames. When I do an AND statement I lose all the records where the forename is blank. All the examples I have seen don't seem do deal with blanks
@@prajac Thanks for the question. Here’s a video that shows using multiple criteria with the FILTER function, ua-cam.com/video/A5N3p408tF0/v-deo.html.
To have the FILTER function return rows with a blank value in the forename column, you can use the ISBLANK function in the second parameter of FILTER. For example, assuming forename is in column A and surname is in column B, you could use:
=FILTER(A2:B4,(ISBLANK(A2:A4))*(B2:B4="Smith”),”-“)
The above equation will return matches for any name pairs whose forename is blank and surname is equal to “Smith”.
Hope this helps.
@@ExcelSmith Hey, Thank you for the fantastic videos!! May I ask you a question regarding FILTERing with wildcards? I'm really grinding my brain on this the past days; just can't wrap my head around it. The real question would be why the super helpful FILTER functionality falls a bit short when it comes to wildcards, but for now:
How can I point to a cell that contains letters or numbers and tell the filter: find the cells where words begin(!) with the letters in this cell (A4 for example)?
I know it is basically the same question as in the banana/pearbanana-question from prajac, but I just cannot fit the =1 in so that it works. Might be an issue with referencing a cell in my example.
My current formula is =FILTER(‘Tab’1!A:D,ISNUMBER(SEARCH(A4,’Tab1’!A:A,1)),““) - where A4 contains the letters i want to search with. It works nicely for the most part. But now if A4 contains (the letters) JM, the filter also returns rows where the cells in A:A start with FJM.
(Modifying the JM or going "around the corner" an extra turn by referencing an additional cell that adds * or ? is not really an option since this would sacrifice some ergonomics and automation regarding other aspects of the spreadsheet. The letters in my cell A4 are already mirrored from another place)
Is it possible to get this working or did I just explore the outer boundaries of FILTER? Haha. Any help would be hugely appreciated. Thank you for reading this and keep up the good work!
one more thing just in case: my example formula might not be copy-paste-able because I had to mess around with the ‘ as these wouldn't display correctly.
@@hundeparadies7691 Thanks for the question. As I understand your scenario, you want to filter the values in ‘Tab’!A:D where the values in ‘Tab1!’A:A start with “JM” (eg: “JMA”). However, the formula you’re using is also showing rows that contain “JM” like “FJM”. Assuming the values you want in column A always start with “JM”, you could use this FILTER function:
=FILTER(‘Tab’!A:D,IFERROR(SEARCH(A4,‘Tab’!A:A,1)=1,FALSE),"")
The SEARCH portion of the equation will return TRUE for any values in column A that start with “JM” (eg: “JMA”), FALSE for any values that contain “JM” but that don’t start with it (eg: “FJM”), or a #VALUE! error for any values that don’t contain “JM” anywhere (eg: “ABC”). To ensure the SEARCH function always returns TRUE or FALSE, which is needed by the FILTER function, we can wrap it inside an IFERROR function, setting the second parameter of IFERROR to FALSE so that this is returned when “JM” is not found”
I hope this helps. Thanks.
I'm going insane trying to find a solution to my problem. I need to do exactly what you did in this video when searching for text that contains asterisk characters already. However, the results are not what I need. For example, if my search box contains the value AB*12*, I want it to return the results of ABC123 and ABC124 but it's also returning a result of ZABC123 even though there's no asterisk at the beginning. I understand the search function searches "within the range" but is there anyway to make it only search where the asterisks are and disregard any results that have values at the beginning or where there isn't an asterisk?
Thanks for the question. For Excel to include the wildcard characters (“*” and “?”) in the search, we have to escape them by using a tilde (“~”) in front of them. For example, If you want to search for everything that contains “AB*”, you would enter “AB~**” as the first parameter of SEARCH.
For example, assume the range A2: A5 contains the following values: “AB*12*”; “AB12”; “ZABC123”; “ZAB*C123*”. If we want to filter all cells that contain “AB*” we could use this equation:
=FILTER(A2:A5,ISNUMBER(SEARCH("AB~**",A2:A5)),"") => returns “AB*12*”; “ZAB*C123*”
If you want to filter only the row(s) that start with “AB*” you can use this equation:
=FILTER(A2:A5,IFERROR(SEARCH("AB~**",A2:A5)=1,FALSE),"") => returns “AB*12*”
The important things is that you place a tilde (“~”) before any asterisks or question marks that you want to include in your wildcard search. I hope this helps.
@@ExcelSmith Thank you so much for your reply. I want to clarify. My lookup value in the cell contains asterisks, which are intended to be wildcards. The thing is, if the cell value is AB*123, I would like it to return ABC123 but it's also returning ZABC123 and I don't want it to do that since there isn't an asterisk before the A. The issue is that the SEARCH function searches for the text, including wildcards, WITHIN the text of the lookup range and will automatically include values that have extra characters before or after your lookup value whether or not there are wildcards at the beginning or end. For example, I only want it to return the ZABC123 value if my lookup value is *AB*123 but not if the lookup value is AB*123.
@@BeugsTheLegend Thanks for the clarification. To make sure I understand, when you are searching with “AB*123”, do all of the values you want to find begin with “AB…”? If so, we can check that the result of the SEARCH function equals 1. For example:
=SEARCH("AB*123",A2:A5)=1
The SEARCH function will return 1 for the value “ABC123, and 2 for the value ZABC123. Setting the equation equal to 1 would yield TRUE and FALSE. Next, we would need to replace the ISNUMBER function with the IFERROR function, setting the second parameter to FALSE to account for scenarios that do not contain a match for “AB*123”. This gives us the equation:
=IFERROR(SEARCH("AB*123",A2:A5)=1,FALSE)
This new equation would then be the second parameter of the FILTER equation:
=FILTER(A2:A5,IFERROR(SEARCH("AB*123",A2:A5)=1,FALSE),"")
That equation would return “ABC123”, but not “ZABC123”. I hope this helps, but please let me know if this still doesn’t get you what you’re needing. Thanks.
@@ExcelSmith Thank you so much for the reply. I’m excited to give it a shot. I am leaving for vacation for the rest of the week and I may not be able to try until next week but I will try it and let you know. Thank you so much!
@@BeugsTheLegend You're welcome. I hope it works for you. Have a great vacation.
Thanks !!! ... for reminding me why I DON'T USE EXCEL !!!
Glad I could help. 😀 If you don’t mind me asking, do you use something in place of Excel?
@@ExcelSmith I am using access but i will start to learn python. I hate excel and my criticism was about the software not about your video. I never understood excel and why is so hard to do it any simple thing.
Working in Excel is definitely different from working in Access. I too have recently begun learning Python. It looks like a great language, and has the flexibility to do almost anything with data. Best of luck learning the new language.
@@ExcelSmith Thank you from Brazil! Best of luck for you too! ;)
You are like Canelo but in another universe where he became passionate about excel instead of boxing
LOL. 😀 Thanks for the comment.