Search for Cells Containing Specific Text
This article explains how to search for cells containing specific text in Excel. For example, it can be useful when looking for a keyword like “Product A.” If you just want a quick search, you can use Ctrl + F.
Using the SEARCH Function to Find Specific Text
The SEARCH function returns the position of a specified text within a cell. It is case-insensitive and supports partial matches.
Example in Excel
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product Name | Price | ||
| 2 | Product A | 1000 | =SEARCH(“Product A”, A2) | |
| 3 | Product B | 2000 | =SEARCH(“Product A”, A3) | |
| 4 | Product A Special Edition | 1500 | =SEARCH(“Product A”, A4) | |
| 5 | Product C | 800 |
Steps
- Select the target cells or range.
- Use the SEARCH function to find the specific text. For example, to search for cells containing “Product A,” use the following formula:
Formula Example
=SEARCH(“Product A”, A2)
This formula checks if “Product A” exists in cell A2 and returns its position. If it does not exist, it will return a #VALUE! error. You can write similar formulas like =SEARCH(“Product A”, A3), =SEARCH(“Product A”, A4), etc.
You can apply the formula to an entire range: =SEARCH(“Product A”, A2:A5). Results will correspond to the rows in column C for the range A2:A5.
Returning Yes/No (✓/✗) Instead of Position
Use the following formula to show ✓ if the text exists and ✗ if it does not:
=IF(ISNUMBER(SEARCH(“Product A”, A2)), “✓”, “✗”)
Displayed Result
The SEARCH function returns the position of “Product A” if found. For instance:
- In “Product A,” it returns 1.
- In “Product A Special Edition,” it also returns 1.
- In “Product C includes Product A,” it returns 14 (starting position of “Product A”).
Using the FILTER Function to Extract Cells Containing Specific Text
The FILTER function can extract only those cells that contain the specified text, making it easy to narrow down data based on search criteria.
Example in Excel
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product Name | Price | ||
| 2 | Product A | 1000 | =FILTER(A2:A5, ISNUMBER(SEARCH(“Product A”, A2:A5))) | |
| 3 | Product B | 2000 | ||
| 4 | Product A Special Edition | 1500 | ||
| 5 | Product C | 800 | ||
Steps
- Select the data range (e.g., A2:A5).
- Use the FILTER function to extract cells containing “Product A.”
- Formula format:
Formula Example
=FILTER(A2:A5, ISNUMBER(SEARCH(“Product A”, A2:A5)))
This formula extracts only the rows where “Product A” exists in the range A2:A5.
Displayed Result
The FILTER function extracts cells containing “Product A,” such as “Product A” and “Product A Special Edition.”
Conclusion
- Use the SEARCH function to locate cells containing specific text.
- Use the FILTER function to extract data based on specific text.
- Both functions support partial matches, making them flexible for various data analysis tasks.