How to Find and Export Matching Rows to a New Excel Spreadsheet
Question details
The user needs to extract specific rows containing a target text value from a comma-separated field and export these matching records to a new worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering and extracting data based on partial text matches within cells containing multiple comma-separated values.
- Observed behavior
- The user wants to return all corresponding rows dynamically to a separate spreadsheet without manual copying and pasting.
Ensure your source data is organized with clear column headers. Formatting your data as an Excel Table will make your formulas dynamic and easier to manage.
Use the FILTER and SEARCH Functions
Combine the FILTER and SEARCH functions to dynamically extract rows that contain specific text, even within comma-separated lists.
The FILTER function is perfect for extracting matching records dynamically. By combining it with ISNUMBER and SEARCH, you can locate specific text strings anywhere within a cell, overcoming the challenge of comma-separated values.
Determine the full range of your source data (e.g., Sheet1!A2:AA1000) and the specific column you want to search (e.g., Sheet1!AA2:AA1000).
In your new worksheet, click the top-left cell where you want the exported data to begin. Enter the formula: =FILTER(Sheet1!A2:AA1000, ISNUMBER(SEARCH("Safety Team", Sheet1!AA2:AA1000)), "No matches").
Replace "Safety Team" with the exact text value you are looking for. The SEARCH function ensures it will find the text even if it is part of a comma-separated list.
Press Enter to execute the formula. The matching rows will automatically spill into the adjacent cells, creating a dynamic export of your data.
Format Data as an Excel Table for Dynamic References
Convert your source data into an official Excel Table so you don't have to manually update the formula when new rows are added.
Easily Filter and Export Data with WPS Office
WPS Spreadsheet fully supports advanced dynamic array functions like FILTER and SEARCH, allowing you to seamlessly manage, extract, and export complex data sets. It offers a familiar interface and robust data processing capabilities completely free of charge.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the source data.
- 2. Apply the FILTER formula: Navigate to a new sheet and use the =FILTER(range, ISNUMBER(SEARCH("text", search_column))) formula to extract your rows.
- 3. Export or save the file: Once the data is filtered, you can easily save the workbook or right-click the sheet tab to export the specific sheet as a new file.
Frequently Asked Questions
Can I search for exact matches instead of partial text in comma-separated fields?
Yes. If you only want exact cell matches and not partial text within a comma-separated list, you can simplify the formula by removing SEARCH and ISNUMBER. Use: =FILTER(Sheet1!A2:AA1000, Sheet1!AA2:AA1000="Safety Team", "No matches").
Why is my FILTER formula returning a #SPILL! error?
The #SPILL! error occurs when there isn't enough empty space for the filtered results to populate. Ensure the cells below and to the right of your formula are completely blank and free of any hidden data or merged cells.
Does this method work if the search text is case-sensitive?
The SEARCH function is not case-sensitive. If you need a strict case-sensitive search, replace the SEARCH function with the FIND function in your formula: =FILTER(Sheet1!A2:AA1000, ISNUMBER(FIND("Safety Team", Sheet1!AA2:AA1000))).
Can I filter data based on multiple criteria?
Yes, you can use the multiplication operator (*) for AND logic, or the addition operator (+) for OR logic within the FILTER function. For example, for two conditions, use: =FILTER(A2:C100, (B2:B100="Value1") * (C2:C100="Value2")).
