Google Sheets If String Contains: Formula And Examples

When working with Google Sheets, there are times when you need to check if a cell contains a specific string or text. This can be useful for filtering data, making conditional statements, or even automating tasks. Google Sheets provides several functions to help you achieve this, including the REGEXMATCH, SEARCH, and IF functions. In this article, we'll explore how to use these functions to check if a string contains a specific text or phrase.

Understanding the Functions

Before we dive into examples, let's quickly understand the functions we'll be using:

• REGEXMATCH: This function checks if a string matches a regular expression pattern. It returns TRUE if the string matches the pattern, and FALSE otherwise.
• SEARCH: This function returns the position of a substring within a string. If the substring is not found, it returns a #VALUE! error.
• IF: This function is used to test conditions and return one value for a TRUE result and another value for a FALSE result.

Using REGEXMATCH to Check if a String Contains a Specific Text

One of the most powerful ways to check if a string contains a specific text in Google Sheets is by using the REGEXMATCH function. Here's a basic example:

Formula: =REGEXMATCH(A1, "apple")

In this formula, A1 is the cell that contains the text you want to check, and "apple" is the text you're looking for. If the text in A1 contains "apple", the formula will return TRUE; otherwise, it will return FALSE.

Example Use Case

Suppose you have a list of fruit names in column A, and you want to identify which ones contain the word "apple".

Fruit Name
Apple Juice
Banana
Apple Pie
Orange

You can use the REGEXMATCH function in column B to check each fruit name:

Fruit Name	Contains "apple"
Apple Juice	TRUE
Banana	FALSE
Apple Pie	TRUE
Orange	FALSE

Using SEARCH to Find a Substring

The SEARCH function is another way to check if a string contains a specific text. Unlike REGEXMATCH, SEARCH does not support regular expressions but is useful for simple substring searches.

Formula: =SEARCH("apple", A1)

This formula searches for the substring "apple" within the text in cell A1. If found, it returns the starting position of the substring. If not found, it returns a #VALUE! error.

Using IF with SEARCH

To make the result more readable, you can combine SEARCH with the IF function:

Formula: =IF(ISERROR(SEARCH("apple", A1)), "Not Found", "Found")

This formula returns "Found" if the text in A1 contains "apple", and "Not Found" otherwise.

Using IF and REGEXMATCH Together

You can also use IF and REGEXMATCH together to check for multiple conditions or to return specific values based on the match.

Formula: =IF(REGEXMATCH(A1, "apple"), "Contains apple", "Does not contain apple")

This formula checks if the text in A1 contains "apple" and returns "Contains apple" if true, and "Does not contain apple" if false.

Multiple Conditions

To check for multiple conditions, you can nest IF functions or use the IF function with the AND or OR functions.

Formula: =IF(AND(REGEXMATCH(A1, "apple"), REGEXMATCH(A1, "pie")), "Contains both", "Does not contain both")

This formula checks if the text in A1 contains both "apple" and "pie".

Conclusion

Checking if a string contains a specific text in Google Sheets can be achieved through various methods, including using the REGEXMATCH, SEARCH, and IF functions. These functions provide powerful tools for data analysis, filtering, and automation. By understanding how to use these functions, you can harness the full potential of Google Sheets to streamline your workflow and make more informed decisions.

Google Sheets Functions Image Gallery

We hope this comprehensive guide has helped you understand how to use Google Sheets functions to check if a string contains a specific text. Whether you're a beginner or an advanced user, mastering these functions can significantly enhance your productivity and data analysis capabilities.