Function Problems

How to Find Excel Cells Containing Specific Text and Return a List

Adam Davis Oct 12, 2026 870 views

Question details

Search a spreadsheet range for cells containing specific text (e.g., 'AB') and return a continuous list of all matching cell values.

How to Find Excel Cells Containing Specific Text and Return a List

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering and extracting a complete list of cell values that contain a specific substring or prefix from a larger dataset.
Observed behavior: A continuous list or table containing only the cell values that match the specific text criteria.

Before you start

Ensure your data range is clearly defined and identify the exact text string or prefix you want to search for before applying extraction formulas or queries.

Solution 1Recommended

Use Dynamic Array Formulas (Microsoft 365)

Use a combination of LET, TOCOL, and FILTER functions to dynamically extract matching values into a new list.

This method is highly efficient and updates in real time, but it requires a version of Excel that supports dynamic arrays (such as Microsoft 365 or Office 2021).

Select the target cell

Click on an empty cell where you want the extracted list of matching values to begin.

Enter the formula

Type the formula =LET(C,TOCOL(A1:D4),FILTER(C,LEFT(C,2)="AB")). Replace A1:D4 with your actual data range, and replace "AB" with the specific prefix you want to match.

Execute to spill results

Press Enter. The complete matching values (e.g., AB87, AB094) will automatically spill down the column.

Use Dynamic Array Formulas (Microsoft 365)

Dynamic Updates: The returned list is fully dynamic. If you change the data in the source range, the extracted list will automatically update.

Solution 2

Extract Values Using Power Query

Ideal for larger datasets, older Excel versions, or repeatable extraction processes across different sheets.

Efficient Data Extraction

Extract Specific Text Easily with WPS Spreadsheet

WPS Spreadsheet provides powerful filtering tools, advanced text functions, and seamless array support to help you find and extract data instantly without complex setups.

1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your target data.
2. Apply AutoFilter: Highlight your data range, navigate to the Data tab on the top ribbon, and click the AutoFilter button.
3. Set text criteria: Click the filter drop-down on your column header, choose Text Filter > Contains, and type your specific text (e.g., 'AB').
4. Extract the results: Select the visible filtered cells, copy them (Ctrl+C), and paste them (Ctrl+V) into a new location to create your standalone list.

Fully compatible with standard Excel formulas like FILTER, SEARCH, and LETAdvanced AutoFilter tool for quick text extraction without manual formulasLightweight performance for handling large datasets smoothlyFree to use with an intuitive, tabbed user interface

microsoft office alternative - wps office

Frequently Asked Questions

Can I search for text anywhere in the cell instead of just the beginning?

Yes. To match specific text anywhere within the cell, replace the LEFT function with the SEARCH function in your formula. For example, use =FILTER(C, ISNUMBER(SEARCH("AB", C))) to find 'AB' at any position.

What alternative tools can I use if I don't have Microsoft 365?

If your version does not support dynamic array formulas, you can use Power Query to extract the data, utilize the Advanced Filter feature to copy results to another location, or use a complex array formula combining the INDEX and AGGREGATE functions.

Why does my dynamic array formula return a #CALC! error?

The #CALC! error typically occurs when the FILTER function cannot find any cells that match your specific text criteria. You can prevent this error by adding an 'if_empty' argument to the formula, such as =FILTER(..., "No match found").

Excel & Data Problems