Excel Formula to Filter Product Codes with Specific Letters and Numbers

Managing vast inventory databases with complex alphanumeric product codes is a notoriously tedious struggle for data analysts. While standard data extraction resources-such as basic manual filters or native search tools-provide a starting point, they often fail under complex criteria. Fortunately, leveraging advanced array formulas grants users unparalleled precision and real-time automation.

Note the stipulation: this dynamic approach requires Excel 365 or 2021 to support spill ranges. For example, filtering for specific codes like "TX-901" becomes effortless. Below, we will detail the step-by-step formula combination of FILTER and SEARCH to streamline your workflow.

Managing large inventory sheets, supply chain databases, or product catalogs in Excel can quickly become overwhelming. Product codes-often referred to as SKUs (Stock Keeping Units)-frequently combine letters and numbers to represent specific product categories, manufacturing plants, batches, or specifications. For instance, a product code like TX-902-A might denote a Texas warehouse (TX), a specific parts batch (902), and a quality grade (A).

When you need to extract or filter a subset of these codes based on specific alphanumeric combinations-such as product codes containing the letters "TX" and the number "9"-doing it manually is prone to errors. Fortunately, Excel offers a robust set of functions that make dynamic filtering efficient and automated. This comprehensive guide walks you through the best Excel formulas to filter product codes containing specific letters and numbers, ranging from modern dynamic array formulas to classic legacy workarounds.

Understanding the Core Logic

To filter data in Excel based on text criteria, we need to construct a logical test that answers the question: "Does this product code contain our target sequence of letters and numbers?"

To do this, we combine three essential Excel functions:

• SEARCH (or FIND): Looks for a specific substring within a text string. If found, it returns the starting position of the substring as a number. If not found, it returns a #VALUE! error.
• ISNUMBER: Checks whether a value is a number. By wrapping this around the SEARCH function, we convert the numeric position (a match) into TRUE, and the error (no match) into FALSE.
• FILTER: Introduced in Excel 365 and Excel 2021, this dynamic array function filters a range or array based on a boolean (TRUE/FALSE) array.

Method 1: The Modern Dynamic Formula (Excel 365 & 2021)

If you are using a modern version of Excel, the combination of FILTER, ISNUMBER, and SEARCH is the cleanest and most efficient approach. This formula dynamically spills the filtered list into neighboring cells, updating automatically whenever your source data changes.

Example Dataset

Assume your product codes are located in the range A2:A11:

Scenario A: Filter codes containing a single specific substring (e.g., "9")

To filter all product codes that contain the number "9", enter the following formula in an empty cell where you want the filtered list to start:

=FILTER(A2:A11, ISNUMBER(SEARCH("9", A2:A11)), "No matches found")

How it works: SEARCH("9", A2:A11) evaluates every cell in the range. If "9" is present, it returns its position (e.g., 2, 5, etc.). ISNUMBER converts these positions to TRUE and errors to FALSE. Finally, FILTER extracts only the items marked TRUE.

Scenario B: Filter codes containing BOTH specific letters AND numbers (AND Logic)

What if you want to find product codes that contain the letters "TX" AND the number "9"? In Excel formulas, "AND" logic is represented by multiplying the logical arrays using the asterisk (*) operator.

Enter this formula:

=FILTER(A2:A11, ISNUMBER(SEARCH("TX", A2:A11)) * ISNUMBER(SEARCH("9", A2:A11)), "No matches found")

How it works: Excel treats TRUE as 1 and FALSE as 0. By multiplying the two arrays, only rows where both conditions are TRUE (1 * 1 = 1) will evaluate to TRUE. Any row where one or both conditions are FALSE (1 * 0 = 0) will be excluded.

Based on our dataset, this formula will yield:

• TX-902-A
• NY-902-TX
• B205-TX-9
• TX-101-A9

Scenario C: Filter codes containing EITHER specific letters OR numbers (OR Logic)

If you want to return product codes that contain either "FL" OR the number "8", you use addition (+) instead of multiplication. Addition acts as the "OR" operator in array formulas.

=FILTER(A2:A11, ISNUMBER(SEARCH("FL", A2:A11)) + ISNUMBER(SEARCH("8", A2:A11)), "No matches found")

How it works: If either condition (or both) is TRUE, the sum will be greater than 0, which Excel interprets as TRUE in the FILTER function.

Method 2: Case-Sensitive Filtering

The SEARCH function is case-insensitive. It treats "tx", "Tx", and "TX" identically. If your system distinguishes between uppercase and lowercase codes-for example, where "tx" means one thing and "TX" means another-you must use the FIND function instead of SEARCH.

To run a case-sensitive filter for codes containing uppercase "TX" and "9":

=FILTER(A2:A11, ISNUMBER(FIND("TX", A2:A11)) * ISNUMBER(FIND("9", A2:A11)), "No matches found")

If a product code contains lowercase "tx-902-a", the FIND function will output an error, and that code will be skipped.

Method 3: Helper Column Solution (For Older Excel Versions)

If you are working on Excel 2019, 2016, or older, the FILTER function is not available. To achieve a similar result, you can use a helper column alongside the classic Excel filter tool.

1. Insert a new column next to your product codes (e.g., Column B, titled "Matches Criteria").
2. In cell B2, enter the following formula:

   =IF(AND(ISNUMBER(SEARCH("TX", A2)), ISNUMBER(SEARCH("9", A2))), "Yes", "No")

3. Drag the autofill handle down to copy the formula to the rest of the column.
4. Select your data range, navigate to the Data tab on the Excel Ribbon, and click Filter.
5. Click the filter dropdown arrow at the top of Column B and check only "Yes".

This filters your table in place, showing only the product codes that match your criteria.

Method 4: Dynamic Reference Filtering

Hardcoding your search terms ("TX" and "9") directly into your formulas is not ideal if you need to change your search parameters frequently. A better practice is to point your formula to cell references where you can type your criteria dynamically.

Let's say you write your letter criteria in cell D2 (e.g., "TX") and your number criteria in cell E2 (e.g., "9"). Your dynamic formula becomes:

=FILTER(A2:A11, ISNUMBER(SEARCH(D2, A2:A11)) * ISNUMBER(SEARCH(E2, A2:A11)), "No matches found")

Now, simply changing the text in cell D2 or E2 will immediately recalculate and display the updated list of filtered product codes without needing to rewrite the formula.

Handling Empty Input Criteria

One common pitfall with dynamic references is that if you leave cell D2 or E2 blank, the SEARCH function will treat the blank cell as matching everything, which can skew your results. To safeguard your formula against blank criteria cells, wrap them in an IF condition:

=FILTER(A2:A11, 
  (IF(D2="", 1, ISNUMBER(SEARCH(D2, A2:A11)))) * 
  (IF(E2="", 1, ISNUMBER(SEARCH(E2, A2:A11)))), 
  "No matches found"
)

How this works: If cell D2 is empty, the formula assigns a value of 1 (TRUE) to that entire array condition, effectively bypassing that filter step and only filtering by the remaining active criteria.

Summary

Filtering product codes with specific letters and numbers in Excel is highly customizable depending on your version of Excel and your exact requirements. By combining FILTER with logical operators (* for AND, + for OR) and text evaluators (SEARCH or FIND), you can construct clean, automated dashboards that simplify your product inventory and SKU management tasks.