How to filter numbers end with a specific number in Excel?

Filtering data efficiently is a frequent requirement when working with large datasets in Excel. While the built-in Filter feature in Excel makes it straightforward to filter text strings that end with certain characters, it does not provide a similarly intuitive approach for filtering numeric values that end with specific digit(s). This limitation often arises in scenarios such as processing invoice numbers, product codes, or any numerical identifiers where the ending digits carry meaning. For example, you may need to identify all numbers that end with “00” representing a special category or summarize items ending in certain digits for further analysis. In situations like these, knowing robust workarounds not only saves significant amounts of time, but also helps minimize manual errors.

The following guide demonstrates practical methods to filter numbers ending with specific digits in Excel, including use of a helper column, VBA automation, Advanced Filter, and a discussion of their applicable scenarios, benefits, and potential limitations. These solutions make it possible to flexibly extract relevant numbers based on your project or reporting needs.

Contents:
• Filter numbers end with specific number with a helper column

---

Filter numbers end with specific number with a helper column

Although Excel does not directly offer a built-in function for filtering numbers that end with specific digits, using a helper column in combination with a formula is a straightforward workaround suited to a variety of workplace scenarios. Commonly, this approach is ideal when you have a large dataset and want a visual auxiliary to show whether each value meets your criteria before filtering, making the process transparent and reducing the risk of accidental exclusion.

For example, say you want to identify and filter all numbers in a list that end with "00"—such as for flagging round transaction amounts or identifying status codes.

1. Insert the following formula next to your data column in a new helper column (for example, cell B2, assuming your numbers begin in A2):

=RIGHT(A2,2)="00"

Drag the fill handle down to apply the formula to the cells you want to check. This formula compares the last two digits of the number in column A. If the number ends with “00,” it will return TRUE; otherwise, it will show FALSE. The result visually distinguishes which rows match your specified criterion.

 

Note: In the above formula, A2 refers to the cell containing the original number, 2 specifies how many ending digits to check, and 00 is the digit sequence you want filtered. Adjust these parameters to suit your specific requirements (for example, change “2” to “1” to check only the last single digit).

Tip: When the numbers are stored as numbers (rather than text), the RIGHT function works as Excel treats cell values as text within this function automatically.

2. After confirming the helper column displays TRUE/FALSE results, select your helper column and go to Data > Filter to activate the filter dropdown. Click the dropdown button for your helper column, unselect all except TRUE, as shown below:

3. Click OK to apply the filter and display only rows where numbers end with your specified digits ("00" in this example). All matching values will now be visible, hidden from the rest of your dataset:

Advantages: This approach is simple to set up, easy to understand, and allows you to adjust the filter criterion just by changing the helper formula.
Limitations: It requires creating an extra column, which may not be convenient if you want to keep your table structure untouched.

Troubleshooting: If the helper column always returns FALSE, ensure that your numbers do not have unexpected formatting (such as hidden decimals or leading zeros). Additionally, for numbers with formatting or stored as text, you may need to adjust your formula.

---