Function Problems

How to Return Excel Rows Containing Specific Text Without Filtering

John Wilson Oct 12, 2026 871 views

Question details

The user wants to dynamically extract and return complete rows from a dataset that contain a specific text string (e.g., a specific job name) without manually applying data filters.

How to Return Excel Rows Containing Specific Text Without Filtering

Product: Excel
Device & OS: not provided
Scenario: Creating a dynamic report or data extraction where rows containing partial text matches automatically populate in a designated area.
Observed behavior: The user needs a formulaic approach to pull matching rows so the results update instantly when the source data changes, bypassing the traditional filter menu.

Before you start

Ensure your version of Excel or WPS Spreadsheet supports the dynamic array FILTER function, as older versions may require complex array formulas or the manual Advanced Filter tool.

Solution 1Recommended

Use the FILTER and SEARCH Functions to Extract Rows

Combine the dynamic array FILTER function with ISNUMBER and SEARCH to instantly extract rows containing a partial text match.

The FILTER function is designed to return an array of values based on criteria. By nesting the SEARCH function inside ISNUMBER, you create an array of TRUE/FALSE values that tells the FILTER function exactly which rows contain your specific text.

Identify your data and search range

Determine the full array of data you want to return (e.g., B2:B100) and the specific column where you are looking for the text match (e.g., E2:E100).

Select the destination cell

Click on the top-left cell of the blank area where you want the extracted rows to spill. Make sure there is enough empty space below and to the right so the formula does not return a #SPILL! error.

Input the dynamic formula

Type the formula =FILTER(B2:B4, ISNUMBER(SEARCH("Pier Final Lift", E2:E4))). Adjust the ranges and the text string to match your actual dataset.

Execute the formula

Press Enter. All rows containing your specified text in the lookup column will automatically populate in the designated area.

Use the FILTER and SEARCH Functions to Extract Rows

Case Sensitivity: The SEARCH function is case-insensitive, meaning it will match 'pier' and 'Pier'. If you require an exact case-sensitive match, replace the SEARCH function with the FIND function.

Solve Data Extraction Easily with WPS Office

Extract and Filter Data Dynamically with WPS Spreadsheet

WPS Spreadsheet fully supports modern dynamic array functions like FILTER, allowing you to instantly extract, arrange, and analyze data without relying on complex manual filtering tools. It provides a highly efficient and familiar experience for managing large datasets.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your dataset containing the rows you want to extract.
2. Select an output area: Click on a blank cell where you want the dynamically filtered data to be displayed.
3. Enter the FILTER and SEARCH formula: Input the formula combining FILTER, ISNUMBER, and SEARCH to target your specific text criteria.
4. Press Enter to view results: Hit Enter, and WPS Spreadsheet will instantly spill the matching rows into the adjacent cells, updating automatically if your source data changes.

Full support for advanced dynamic array formulas including FILTER, SORT, and UNIQUE.100% compatibility with Microsoft Excel (.xlsx) files and native functions.Free, lightweight, and fast alternative for powerful data analysis.Clean and familiar user interface ensuring a seamless migration from Microsoft Office.

microsoft office alternative - wps office

Frequently Asked Questions

What should I do if the formula returns a #CALC! error?

The #CALC! error typically occurs when the FILTER function finds no records matching your search text. You can prevent this by adding a third argument to the FILTER function to handle empty results, for example: =FILTER(B2:B4, ISNUMBER(SEARCH("Text", E2:E4)), "No matches found").

Can I search for multiple different job names at once?

Yes. You can combine multiple ISNUMBER(SEARCH()) conditions using the plus (+) operator for OR logic. For example: =FILTER(B2:B10, ISNUMBER(SEARCH("Job1", E2:E10)) + ISNUMBER(SEARCH("Job2", E2:E10))).

How do I extract rows if my software version doesn't support the FILTER function?

If you are using an older version of Excel that lacks dynamic arrays, you can use the Advanced Filter tool (Data > Advanced) to copy rows matching specific criteria to a new location. Alternatively, you would need to use a complex array formula combining IFERROR, INDEX, SMALL, IF, and ROW.

Excel & Data Problems