How to filter numbers end with a specific number in Excel?
In Excel, it provides a built –in Filter feature to filter text strings which end with a specific character, but, there is no similar feature for you to filter numbers end with a specific number as following screenshot shown. In this case, how could you filter numbers end with specific number in Excel?
There is no direct way for you to filter numbers end with specific number, here, you should create a helper column firstly, and then filter the numbers based on this helper column. Please do as follows:
For example, here I want to filter all numbers end with 00 as below screenshot shown:
1. Enter this formula: =RIGHT(A2,2)="00" beside your data cell, B2, for instance, and then drag the fill handle down to the cells which you want to apply this formula. And True will be displayed if the corresponding number ends with 00, otherwise, False will be displayed, see screenshot:
Note: In the above formula, A2 is the number cell you want to use, the number 2 indicates two numbers will be found from end number, and 00 is the specific numbers you want to filter end with. You can change them to your need.
2. Then select the helper column, and click Data > Filter to activate the filter function, and then click drop down button, under the Select All section, only check the TRUE option, see screenshot:
3. And then click OK button, the numbers end with 00 have been filter out, see screenshot:
Best Office Productivity Tools
Supports Office/Excel 2007-2021 and 365 | Available in 44 Languages | Easy to Uninstall Completely
Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need Is Just A Click Away...
Supercharge Your Excel Skills: Experience Efficiency Like Never Before with Kutools for Excel (Full-Featured 30-Day Free Trial)
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day! (Full-Featured 30-Day Free Trial)