Intro
Master Excels Contains function with 5 powerful methods to search partial text. Discover how to harness wildcards, criteria ranges, and VLOOKUP to efficiently extract data. Boost productivity with expert techniques for using Excel Contains partial text, empowering you to analyze and filter data with ease.
When working with data in Excel, there are often instances where you need to identify cells that contain specific text or phrases. This can be a tedious task, especially when dealing with large datasets. Fortunately, Excel offers several ways to find and manipulate cells containing partial text. In this article, we will explore five methods to use Excel to find cells that contain partial text.
Understanding Partial Text in Excel
Before diving into the methods, it's essential to understand what partial text means in the context of Excel. Partial text refers to a situation where you're searching for a specific word or phrase within a larger text string. For example, if you have a list of product names, you might want to find all products that contain the word "apple" regardless of their position in the text string.
Method 1: Using the FIND Function
The FIND function in Excel returns the position of a specified text string within another text string. If the text string is not found, it returns a #VALUE! error. The syntax for the FIND function is:
FIND(find_text, within_text, [start_num])
Where:
- find_text is the text you want to find.
- within_text is the text you want to search.
- [start_num] is the position in the within_text where you want to start searching.
You can use the FIND function in conjunction with the IF function to test if a cell contains a specific text string. For example:
=IF(ISNUMBER(FIND("apple",A1)),"Found","Not Found")
This formula checks if the word "apple" is found in cell A1. If it is, the formula returns "Found"; otherwise, it returns "Not Found".
Example Use Case
Suppose you have a list of product names in column A, and you want to identify products that contain the word "apple". You can use the FIND function in column B to achieve this:
| Product Name | Contains Apple? |
|---|---|
| Apple iPhone | Found |
| Samsung TV | Not Found |
| Apple Watch | Found |
| Google Pixel | Not Found |
Method 2: Using the SEARCH Function
The SEARCH function in Excel is similar to the FIND function, but it is not case-sensitive. The syntax for the SEARCH function is:
SEARCH(find_text, within_text, [start_num])
Where:
- find_text is the text you want to find.
- within_text is the text you want to search.
- [start_num] is the position in the within_text where you want to start searching.
You can use the SEARCH function in the same way as the FIND function to test if a cell contains a specific text string. For example:
=IF(ISNUMBER(SEARCH("apple",A1)),"Found","Not Found")
This formula checks if the word "apple" is found in cell A1, regardless of the case. If it is, the formula returns "Found"; otherwise, it returns "Not Found".
Example Use Case
Suppose you have a list of product names in column A, and you want to identify products that contain the word "apple" regardless of the case. You can use the SEARCH function in column B to achieve this:
| Product Name | Contains Apple? |
|---|---|
| Apple iPhone | Found |
| Samsung TV | Not Found |
| Apple Watch | Found |
| Google Pixel | Not Found |
| APPLE TABLET | Found |
Method 3: Using the COUNTIF Function
The COUNTIF function in Excel counts the number of cells in a range that meet a specified condition. You can use the COUNTIF function to count the number of cells that contain a specific text string. The syntax for the COUNTIF function is:
COUNTIF(range, criteria)
Where:
- range is the range of cells you want to search.
- criteria is the condition you want to apply.
You can use the COUNTIF function with a wildcard character (*) to search for cells that contain a specific text string. For example:
=COUNTIF(A:A,"apple")
This formula counts the number of cells in column A that contain the word "apple".
Example Use Case
Suppose you have a list of product names in column A, and you want to count the number of products that contain the word "apple". You can use the COUNTIF function to achieve this:
| Product Name | Count |
|---|---|
| Apple iPhone | 3 |
| Samsung TV | 0 |
| Apple Watch | 3 |
| Google Pixel | 0 |
| APPLE TABLET | 3 |
Method 4: Using the FILTER Function
The FILTER function in Excel returns a filtered range of values based on a specified condition. You can use the FILTER function to filter a range of cells that contain a specific text string. The syntax for the FILTER function is:
FILTER(range, condition)
Where:
- range is the range of cells you want to filter.
- condition is the condition you want to apply.
You can use the FILTER function with a wildcard character (*) to filter a range of cells that contain a specific text string. For example:
=FILTER(A:A,""&"apple"&"")
This formula filters the cells in column A that contain the word "apple".
Example Use Case
Suppose you have a list of product names in column A, and you want to filter the products that contain the word "apple". You can use the FILTER function to achieve this:
| Product Name |
|---|
| Apple iPhone |
| Apple Watch |
| APPLE TABLET |
Method 5: Using Power Query
Power Query is a powerful tool in Excel that allows you to manipulate and transform data. You can use Power Query to filter a range of cells that contain a specific text string.
To use Power Query, follow these steps:
- Go to the Data tab in Excel.
- Click on "From Table/Range" in the Get & Transform Data group.
- Select the range of cells you want to filter.
- Click on "OK".
- In the Power Query Editor, click on "Add Column" in the Add Column group.
- Enter the formula:
= Text.Contains([Product Name], "apple") - Click on "OK".
- Click on "Filter" in the Home group.
- Select the column you created in step 6.
- Click on "OK".
This formula filters the cells in column A that contain the word "apple".
Example Use Case
Suppose you have a list of product names in column A, and you want to filter the products that contain the word "apple". You can use Power Query to achieve this:
| Product Name |
|---|
| Apple iPhone |
| Apple Watch |
| APPLE TABLET |
Excel Contains Partial Text Image Gallery
In conclusion, finding cells that contain partial text in Excel can be achieved using various methods, including the FIND function, SEARCH function, COUNTIF function, FILTER function, and Power Query. Each method has its own advantages and disadvantages, and the choice of method depends on the specific requirements of your project. By understanding the different methods and their applications, you can improve your productivity and efficiency when working with data in Excel.
We hope this article has been informative and helpful. If you have any questions or need further clarification, please don't hesitate to ask.