How do i search for text in a cell in google sheets?

If you’re trying to search a string in Google Sheets to see if a cell contains a certain value, there is not a built-in function that will accomplish this.

However, there are a few other functions that can be used to do something similar

In this tutorial, I will show how to search a cell to see if it contains a certain value in Google Sheets.

If Cell Contains with REGEXMATCH

One of the best ways to search your data to see if a cell contains a certain value is to use the REGEXMATCH function.

This function will search a cell and return TRUE if a piece of text matches your regular expression, or FALSE if it does not.

The syntax of this function is:

REGEXMATCH(text, regular_expression)

• Text is the text or reference to the cell you are search
• Regular expression is the text or values you are searching for. You place the text you want to search for inside quotations marks as the second parameter. You can also search for more than one thing with the or symbol “|”

Here is how to use this function to search a cell and see if it contains a certain value:

1. Select the cell where you want the formula to evaluate and type =REGEXMATCH to start your formula

2. The first parameter is the cell you want to search. Select the cell that you want to search and type a comma (,)

3. The second parameter is the text you are searching for. Type your text inside quotation marks and add your closing parenthesis “)”

4. Press ENTER and copy your formula down to any additional cells you want to repeat the formula on

The formula evaluates to either TRUE or FALSE. You can see in this example any cell that contains the word “boy” returns TRUE because that is what my REGEXMATCH formula is set to search for.

The REGEXMATCH function is very useful, but there are a few things to note about how it works.

The first thing you need to know is that it is case-sensitive. If your word has a different capitalization than what you are searching for it will return FALSE every time.

If you want to search for every iteration of the text whether it is capitalized or not, you can do that in two ways.

1. Using the or symbol (|) – you can use the or symbol (|) in your formula to search for multiple things. To use it in the above example to search for “boy” with a capital B and lowercase B you would change your formula to this =REGEXMATCH(A2,“boy|Boy”). This will return true if your cell contains boy OR BOY.
2. Using the LOWER function – another clever option is to add the LOWER function to your formula to automatically convert your text to lowercase before you search it. To do this you would change your formula to =REGEXMATCH(lower(A2),“boy”). This is a better method because then you don’t need to worry about how the text is capitalized

Another important thing to know about the REGEXMATCH function is that it searches for text only. If you want to search for a number, you will have to have that cell formatted as text first before your formula would work

If Cell Contains with IF & SEARCH

Another clever option for searching if a cell contains a certain number or text is to use a combination of the IF and SEARCH Functions.

You would combine them with this syntax:

=IF(SEARCH(“text”, A2) > 0, 1, 0)

This formula will evaluate whether the cell you are searching (A2) contains your “text”. If it does, the formula will evaluate to 1, if not it will result in an error showing #VALUE!

The good thing about this formula is that it can also be used to search for numbers without having to format your cell as text like in the previous example.

Here’s how you would use it:

You can see in this example I am searching cell A2 for my text “boy”. If the cell contains it, the formula evaluates to 1, and if it does not it is showing #VALUE!

This method has benefits over the previous method. It is not case sensitive and you can search for numbers with this formula.

If Cell Contains with COUNTIF

Another way to do this is with the COUNTIF function.

The syntax you will be using for this formula is:

=COUNTIF(A2,“*text*”)

That formula will search your cell and if it contains your text it will return 1, otherwise, it will return 0.

You need to make sure you have the asterisk (*) surrounding the text you are searching for because this stands for a wildcard in your formula. This means it will search for your text anywhere in the cell instead of just looking for an exact match on your entire cell.

Here is how you would use it in a formula to search a cell:

In this example, I am searching the cell to see if it contains “boy”. If it does, the formula evaluates to 1, if not it evaluates to 0.

This method is not case sensitive, but it does not search numbers.

If you want to search for a number you will have to convert the cell you are searching to text for the formula to work properly.

Closing Thoughts

These methods for searching if a cell contains a certain string of text are very useful. Each method has its pros and cons, but they can all be used for the same result.

We recommend practicing and really trying to understand how to use each method and why it works.

We hope you found this helpful!

More Google Sheets Tutorials:
How to Use Does Not Equal
How to Do Multiple If Statements
How to Count Unique Values
Using the TRUE Function