I can't solve today's question using the regular Filter. I am sure someone has a better way. A person in my Milwaukee seminar wants to filter text account numbers to everything between 6 & 9. Do you have a solution?
Transcript of the video:
Learn Excel from MrExcel podcast, episode 2278. Filter between when numbers are stored as text.
Hey welcome back to MrExcel netcast, I’m Bill Jelen.
Last week, the four easy steps to make a difference as an Excel insider. We said we’re going to give away five copies of this awesome vintage Microsoft Office Insider hat. The winners, right here. So hey, congratulations to derecanmexit, Matt Schoular, Audrey Lee, pmsocho and brianxyz.
If you’re in that list, go back to that video and send me an email and let me know where to send the stuff. And actually, anyone who entered will at least get a… some nice I heart Excel decals so that check that out.
Today’s question came from my seminar last week in Milwaukee. And for those of you who wanted to catch me on the seminar, I’m retiring.
I only have six seminars left. So if you’re in Lexington, Frisco, Chicago, Springfield, look at that Chicago smart, they booked me twice or Chattanooga. That’s it after this year, after 2019, I’ll only be doing seminars in about 22 counties in Florida. So please check me out if you want to see one of my seminars. Alright.
So the question came in. Someone has a series of account numbers. And had these account numbers been numeric, this would have been easier to do. They want to be able to filter to anything that starts with 6, 7, 8 or 9, alright. So we go to data and filter. So I can come in here and say number filters between, and say I want to look for anything greater or equal to 6 1 2 3 4 5 and less than or equal to 9 9 9 9 9 9, click OK and I get just the things that start with 6 7 8 or 9, alright.
That’s fine but that’s not what they had.
They actually had text and even worse, they’re not all six digits. There’s some extra digits out here. But let’s just try and solve this first. So I came in and said, well we don’t have number filters and we now have text filters and there’s no longer a between. There is a custom filter and actually hey, here’s a challenge. I can’t figure out how to make this work if I said is greater than or equal to like maybe 6* and is less than or equal to 9*. Right that returns all of the 9s but it doesn’t get me any 6s and I’ve tried various things here and none will work. Now I know someone watching this video is going to say, “Bill, there’s an easy way to do that.” And if there is, just let me know down there in the comments and I’ll be happy to see this. But the only thing I can come up with live there on the spot is to say, alright, we’re going to go back and use the old, old-fashioned advanced filter.
So to use the advances filter, you have to create a criteria range or worse heading and copy it over here and what we want is, we want 6*, 7*, 8* and 9* like that and that becomes our criteria range. So we come back to the original data, advanced filter, we’re going to filter in place and we’re going to specify criteria range of that heading plus the 6 7 8 9 and click OK and I get everything that starts with a 6 through a 9. That’s the best I can come up with. What do you have?
‘m sure you have a better way.
Hey, if you like these videos, please subscribe and make sure to ring the bell. And if you have any questions, for future videos, post those in the comments below. I want to thank you for stopping by. See you next time for another netcast from MrExcel.
Hey welcome back to MrExcel netcast, I’m Bill Jelen.
Last week, the four easy steps to make a difference as an Excel insider. We said we’re going to give away five copies of this awesome vintage Microsoft Office Insider hat. The winners, right here. So hey, congratulations to derecanmexit, Matt Schoular, Audrey Lee, pmsocho and brianxyz.
If you’re in that list, go back to that video and send me an email and let me know where to send the stuff. And actually, anyone who entered will at least get a… some nice I heart Excel decals so that check that out.
Today’s question came from my seminar last week in Milwaukee. And for those of you who wanted to catch me on the seminar, I’m retiring.
I only have six seminars left. So if you’re in Lexington, Frisco, Chicago, Springfield, look at that Chicago smart, they booked me twice or Chattanooga. That’s it after this year, after 2019, I’ll only be doing seminars in about 22 counties in Florida. So please check me out if you want to see one of my seminars. Alright.
So the question came in. Someone has a series of account numbers. And had these account numbers been numeric, this would have been easier to do. They want to be able to filter to anything that starts with 6, 7, 8 or 9, alright. So we go to data and filter. So I can come in here and say number filters between, and say I want to look for anything greater or equal to 6 1 2 3 4 5 and less than or equal to 9 9 9 9 9 9, click OK and I get just the things that start with 6 7 8 or 9, alright.
That’s fine but that’s not what they had.
They actually had text and even worse, they’re not all six digits. There’s some extra digits out here. But let’s just try and solve this first. So I came in and said, well we don’t have number filters and we now have text filters and there’s no longer a between. There is a custom filter and actually hey, here’s a challenge. I can’t figure out how to make this work if I said is greater than or equal to like maybe 6* and is less than or equal to 9*. Right that returns all of the 9s but it doesn’t get me any 6s and I’ve tried various things here and none will work. Now I know someone watching this video is going to say, “Bill, there’s an easy way to do that.” And if there is, just let me know down there in the comments and I’ll be happy to see this. But the only thing I can come up with live there on the spot is to say, alright, we’re going to go back and use the old, old-fashioned advanced filter.
So to use the advances filter, you have to create a criteria range or worse heading and copy it over here and what we want is, we want 6*, 7*, 8* and 9* like that and that becomes our criteria range. So we come back to the original data, advanced filter, we’re going to filter in place and we’re going to specify criteria range of that heading plus the 6 7 8 9 and click OK and I get everything that starts with a 6 through a 9. That’s the best I can come up with. What do you have?
‘m sure you have a better way.
Hey, if you like these videos, please subscribe and make sure to ring the bell. And if you have any questions, for future videos, post those in the comments below. I want to thank you for stopping by. See you next time for another netcast from MrExcel.
