FILTER Function to Extract Records by Area Code. Excel Magic Trick 1843
HTML-код
- Опубликовано: 15 июл 2024
- Download Excel File: excelisfun.net/files/EMT1843....
Learn about how to use FILTER and SEARCH to filter a data set by area code.
Topics:
1. (00:00) Introduction
2. (00:05) FILTER and SEARCH functions to filter by Area Code.
3. (02:04) Summary
4. (02:13) Closing, Video Links
#excel #excelisfun #analytics #analysis #dataanalysis #dataanalytics #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftexcel #microsoftmvp #lookup #xlookup #Filter #Search #filterfunction #filterfun
This is a bad video because it will find any 206, rather than the area code at the beginning. I have had a hard time not using Excel in over 2 months (after using Excel everyday for over 20 years) and taking care of my elderly Mom for over 2 months... Luckily, our Teammates have my back.
ExcelLambda, kimengelmajer8104, sjn7220, abdallahdataguy pointed this out and suggested these other better methods:
=FILTER(Data!B3:F14,IFERROR(SEARCH(B5,Data!F3:F14)
This channel has literally saved my consulting career. I am a partner now.
Glad to help : )
Easy Microsoft 365, thank you Mike!
You are welcome!
in two minutes a great display of advanced excel...you are the number one Mike
#2 for this: read pinned comment. Team was #1 here : )
Hi, great vid as usual but.... this would also return rows in which the area code is NOT 206 but where the phone number contains 206... easily fixed with the inclusino of the bracket in the search or by specifying the resul must be 2, not any number.
Good point. This is more robust.
Good catch. Should be something like
ISNUMBER(SEARCH(LEFT(B5,4),Data!F3:F14))
OR MID(Data!F3:F14,2,3)=B5 etc
So true! How did I miss that!?!? Luckily we have a great Team!! Thanks kim en gel majer!!!!
@@abdallahdataguy , Thanks for the formulas, Teammate!!!!!
I added all of your EXCELlent points to the pinned comment at the top. Thanks, Team!!!!
So simple, yet so complicated. Thanks Mike.
The endless conundrum of Excel lol
Thanks for simplifying the method...
You are welcome!!!!
Another excellent video. Thank you for the good work
You are welcome, Chicken R1ce!!!
Thanks Mike. Great video.
Thanks, WRH!!!!
Thanks Mike for this EXCELlent video.
You are welcome Fellow Teacher!!! : ) : )
Excellent video, Thanks!
You are welcome!!!!
Another amazing video on Excel! Thanks for sharing!
Another way to generate the filtered table would be to use a formula like this:
=IFERROR(FILTER(Data,LEFT(Data[Phone],5)=TEXT(AreaCode,"(###)")),"Invalid area code")
Where Data is the original data range (now converted to a table) and AreaCode is a cell into which 3 area code digits are entered. The TEXT() function returns the formatted area code, e.g. "506" -> "(506)".
Thank you kindly.
Thank you for the great formula. See my pinned comment about my mistake lol
In the past it was much more difficult. I remember watching one of your greatest videos explaining when to use rows and when to use countif for the k of the small function when extracting data
That was a good video. Glad you got to watch it, Nader : ) : ) : )
Thanks, Mike! Another great video showcasing the reason that all companies should move up to MS 365 or get left behind in the technology race; it won't be long now before those of us that can still use the old ways of stringing function together to get a result will all be retired and they have nobody left with the knowledge to repair their most important spreadsheets.
Hope your mom is well on the road to recovery.
Looking forward to your next video.
Your are always Rok mr mike ❤
Great trio formula combinatuon
Glad you like it!!!!!
Genius Mike thanks!
You are welcome, Chris M!!!!!
minute 0:16 brings a lot of good memories 😁
Old School : ) : ) : )
@@excelisfun Old School is still fun RAD Mike.
Thanks Mike, but maybe it could be more accurate to filter only area code = 206, because in this current formula if you have 206 at the end of the number we will have it on the list, but with a different code area. :)
You are correct. I messed this one up. See my comment pinned to top. And, Thanks for your insight, Teammate!!!
Where is new video ...❤
@@ubaidillahmuhammad20I have been in California taking care of my sick and elderly mom. But I will have a few videos out over the next week to look at the amazing new GROUPBY, PIVOTBY and new Lambda Helper Function features!!!!
FC Krub form Thailand
: ) : ) : ) : )
thanks mike ,
is this fourmula help to solve proplem if code erea found in tel number
=FILTER(Data!B3:F14,ISNUMBER(SEARCH(B5,TEXTSPLIT(Data!F3:F14,"-"))))
we wait your completed solve
Thanks Mike, hope things are going okay with your mum. This is great but in a very large data set it's conceivable that you could have the area code in the actual phone number. Perhaps something along the lines of =FILTER(B3:F14,LEFT(F3:F14,5)="("&I2&")") or =FILTER(B3:F14,TEXTBEFORE(F3:F14,"-")="("&I2&")") could be a workaround. I2 being only the numbers in AC
Thanks Teammate for your insight. See my comment pinned to top.
Thanks Mike. A Great Video!! :) :) Just out of curiosity, will we see Python in the future?
Not till after I finish helping mu Mom!!! You are welcome, Formula Guy John!!
Awesome trick Mike! Do you have a preferred contact route for your subscriber questions?
Just comments here. But I have been involved in 2 month family emergency, so I probably will not get to it. But do post question.
@@excelisfun Appreciated wishing you and your family well. Will follow up a concise Q shortly. Best
I have an excel workbook that gets exported from a cloud database. One of the custom columns is formatted as date but the workbook always interprets it
as a 'General' (I have tried convert/cast/etc. it makes no difference). I am using a countifs function to count dates for other columns which works well. I have ran out of ideas for example if I try to
use the datevalue within countifs it complains of too many arguments. Is there a way? Unfortunately I cannot alter the workbook as it will not accept
vba or data models/macro workbooks. End users can run the report direct as a template so Excel pre 2016 formulas are ok.
@@garethwoodall577, You cab try posting to the best Excel question site that I know: mrexcel.com/board
Hi, you mentioned ZIP code at the beginning of your video when you actually meant Area Code instead (You might want to correct it). Secondly, if you have any of the Area Codes 3 numbers also repeating in the phone number string, FILTER would include those rows in your resulting table as well, even if the actual Area Code is not the one you want. You might want to restrict the Area Code SEARCH just to the initial portion of the entire phone number by using MID function (old school) or using by TEXTBEFORE before the closed parenthesis of the Area Code, but in this case including the open parenthesis before the Area Code (M365).
Yah, I messed this video up. I have been in a family emergency with my Mom for two months. So everything is off wack. See my pinned comment at top.
Thanks for your insight, Teammate!!!!!
A different topic... How does one highlight an entire row in an Excel table (say yellow) when one or a few cells in a row change? Is there not a way to do this using conditional formatting?
Here is video: ruclips.net/video/XCR3ReuRnTk/видео.html
@@excelisfun Thank you! A great solution.
Dear Mike, Need your support, How to split text by length without breaking words?
Interesting Challenge @BaniMoniah.
Could You please share an example of the Input Value and the Expected Output Value.
Thank You!
"Fill Justify" feature?
Alt H FI J
@@ankursharma6157
ABC DEFG HIJ
and I need to split the texts for 10 digits
The results should be
ABC DEFG, next cell HIJ ( as if I take 10; the last word HIJ will spilt to non word)
@@viktorasgolubevas2386 I think you didn’t get the question 😅
@@BaniMoniah
I simply referred to an "intelligent" built-in feature which works in the same manner.
No formulas - just in place 🙂