Are you an analyst who frequently works with Google Sheets? If so, you probably know that filtering data is a crucial step in your analysis process. While Google Sheets offers a variety of filtering options, there are times when users require a more advanced approach.
One such instance is when you need to filter entries that meet the criteria of “containing multiple texts” in a Google Sheets spreadsheet. In this article, we will explore two methods that require some formula expertise to accomplish this task.
2 Methods to Filter Entries Based on “Containing Multiple Texts” Criteria in Google Sheets
The “containing multiple texts” criteria means that a cell value should include all the specified text criteria. This requires the use of the logical AND operator, which both of the following methods employ.
Let’s take a closer look.
Method 1: Creating a Custom Formula with the AND Logic in Google Sheets Filter
While Google Sheets allows you to filter for “text containing” by default, it can only consider one value at a time in a single column. To overcome this limitation, we need to create a custom formula that meets the user’s requirements. This custom formula will be based on two conditions:
- Text matching: This can be achieved using a regular expression with the REGEXMATCH function.
- The logical AND operator: Both text matches must evaluate to TRUE.
Step 1: First, let’s create the custom formula that we will apply to the filter. This will help us understand how conditions can be grouped together in a single formula.
To start, we open the AND function to ensure the conditions act according to the AND logic.
Step 2: Apply the two conditions for the multiple texts. We can use the REGEXMATCH function for this.
To match the text “Good,” we have:
REGEXMATCH(A2, "Good")And for “Pitcher,” we have:
REGEXMATCH(A2, "Pitcher")Step 3: Close the parentheses and press Enter. You can use the fill handle to check each row.
This is the custom formula that we will apply to the filter.
Step 4: Apply this formula in the “Custom formula is” field in the Google Sheets filter menu for the “Review” column.
Filter > Filter by condition > Custom formula is > Enter the custom formula
Step 5: Click OK to apply the filter for multiple “Contains text” criteria in Google Sheets.
As an alternative, you can also use an asterisk or the multiplication symbol (*) instead of the AND function to apply the logical AND. The formula would be:
=REGEXMATCH(A2, "Good") * REGEXMATCH(A2, "Pitcher")Learn more about this method on Crawlan.com.
Method 2: Using the FILTER function for “Containing Multiple Texts” Criteria in Google Sheets (Different Location/Sheet)
The second method to filter for multiple “containing text” criteria in Google Sheets uses the FILTER function.
This incredibly versatile function allows you to customize the conditions and offers a few other advantages, which we will see shortly.
As a function, FILTER separates the data either within the same sheet or in a separate sheet.
Step 1: Open the FILTER function in the desired location. For this example, we will present the filtered data in a separate sheet.
Step 2: Apply the data range from the source dataset.
Step 3: Apply the two text conditions. Again, these will be the REGEXMATCH formulas we used earlier. Separate the conditions with a comma, as the FILTER function defaults to the AND logic.
Step 4: Close the parentheses and press Enter. You will be directed to the sheet with the FILTER function that contains the results.
Find more details about this method on Crawlan.com.
Filtering for the “Does Not Contain Multiple Texts” Criteria
You can also choose to filter for the opposite condition, that is, filter entries that “do not contain multiple texts” in Google Sheets.
To do this, simply modify the previously generated custom filtering formulas to their opposite values. In other words, submit them to a logical NOT condition.
For example, to modify the custom filtering formula to display entries except for “Good” and “Pitcher,” simply add the “=FALSE” condition after the criteria (REGEXMATCH).
And for the FILTER formula, again set the condition to FALSE for the REGEXMATCH criteria.
Learn more about this on Crawlan.com.
Final Thoughts
With a good understanding of logical formulas, it is quite easy to filter values that meet the criteria of “containing multiple texts” in Google Sheets.
If you are using Google Sheets’ default filter function, it is essential to generate a custom formula for the condition. However, with the FILTER function, you can apply and present the filtering condition anywhere, whether in the same sheet or a different sheet.
Feel free to leave your questions or tips in the comments section below.
Want to learn more about Google Sheets’ features and discover other online marketing tricks? Visit Crawlan.com for the latest information!
