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?
Filter numbers end with specific number with a helper column
Filter numbers end with specific number with a helper column
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
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
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!