How to Filter Rows with Multiple Codes in One Cell in Excel
Question details
The user needs to extract specific rows from a dataset where a target trial code is embedded alongside other text or codes within a single cell.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Filtering and retrieving data rows based on a partial text match when target keywords are mixed with other data in a specific column.
- Observed behavior
- Standard filtering or exact match formulas fail to return the desired rows because the target cell contains multiple codes instead of just the exact search term.
Ensure your dataset is properly structured and identify the specific column containing the mixed trial codes, as well as the exact target code you want to search for.
Use FILTER with ISNUMBER and SEARCH Functions
Combine the FILTER function with ISNUMBER and SEARCH to locate partial text matches within a cell and dynamically return the corresponding rows.
The SEARCH function looks for your specified trial code within the target column. If it finds the code, it returns a number indicating its starting position; if not, it returns an error. The ISNUMBER function then converts any numeric result into TRUE and any error into FALSE. Finally, the FILTER function uses this array of TRUE/FALSE values to extract only the rows that match your criteria.
Click on the blank cell where you want the top-left corner of your filtered data to appear.
Start your formula by typing =FILTER( and then select the entire data range you want to return (for example, OnlineSprayBookAllData[[Date]:[Photo or Video]]).
For the include argument, type ISNUMBER(SEARCH(target_code, search_column)). Replace target_code with the cell reference of the trial code you are looking for, and search_column with the column range containing the mixed codes.
Add an optional text string for the 'if_empty' argument, such as "Please check GOA code is entered", and close the parentheses. Press Enter to execute the formula.

Easily Filter Complex Data with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like FILTER, along with ISNUMBER and SEARCH, making it easy to extract and analyze complex datasets with mixed text strings.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your mixed trial codes.
- 2. Apply the filtering formula: Select a blank cell and enter the =FILTER(range, ISNUMBER(SEARCH(target, column))) formula.
- 3. Execute the filter: Hit Enter, and WPS Spreadsheet will instantly spill the matching filtered rows into the adjacent cells.

Frequently Asked Questions
Why does FIND not work for filtering multiple rows in this scenario?
While FIND can be used for case-sensitive searches, it may not return an array of values correctly in older spreadsheet environments unless wrapped as a legacy array formula. SEARCH handles array operations natively in this context and is case-insensitive, making it more robust for general partial matches.
Can I search for multiple different trial codes at the same time?
Yes, you can combine multiple ISNUMBER(SEARCH()) conditions within the FILTER function. Use the plus sign (+) between conditions for an OR logic (if either code is present), or an asterisk (*) for an AND logic (if both codes must be present).
What happens if the trial code is not found in any row?
If the code is not found, the FILTER function will return a #CALC! error by default. You can prevent this by adding a custom text message in the third argument of the FILTER function, which will display instead of the error.
Do these filtering functions work on older spreadsheet versions?
The FILTER function is a dynamic array function available only in modern spreadsheet software like WPS Office and Microsoft 365. If you are using a much older version, you would need to use a complex combination of INDEX, AGGREGATE, and ROW functions instead.




