In previous article, I mentioned how to export google calendar to google sheets but in this article, I have mentioned how we can use If Contains Google Sheets Formula or if text contains in Google sheets.
Are you trying to search a string in Google Sheets to see if a cell contains a value? Well, Google Sheets has no inbuilt function for this, but there are functions that let you do something similar.
In this article, you will learn how to search a cell in Google Sheets to see if it contains a value.
How To Check If A Cell Contains A Value In Google Sheets
We shall discuss three methods to check if cells contain a value in Google Sheets. Read on!
Method One: If Cell Contains Using REGEXMATCH
Using the REGEXMATCH function is one of the best ways to check your data if a cell contains a value. This function will search a cell and return TRUE if a piece of text is in the cell, or FALSE if it does not. The syntax of the REGEXMATCH function is:
=REGEXMATCH(text, match)In this syntax, "text" refers to the cell, while "match" is the value you are searching for. You have to place the text you search for in quotation marks as the second parameter. You can also search for more than one value using the or (|) symbol.
Follow these steps to use REGEXMATCH to check if a cell contains a value:
- Select the cell you want the formula to evaluate, then type =REGEXMATCH to start the formula.
- Put the name of the cell you want to search as the first parameter in the parenthesis next to the function search. You can type in the cell name, but it is best to click on the cell to auto-fill its name. Next, type a comma (,).
- Next, enter the text you want to search within quotation marks in the parenthesis and close the parenthesis.
- Press the "Enter" key and the results will appear in the cell.
- The app will also suggest an auto-fill and you can allow it by pressing Ctrl + Enter or clicking the Mark icon in the suggestion prompt.
- Copy the formula to the other cells you need to check and repeat the same process, substituting the previous cell name in the formula with the new cell name you want to search.
In the example above, the cells that contain the word "dog" return a TRUE result, while the ones that do not contain it will return FALSE.
While the REGEXMATCH formula is helpful, it may not be as easy as it looks. The formula only searches for texts, and you must format any number you want to find to text. Also, it is case sensitive, and capitalization would return a FALSE result.
However, you can bypass its case sensitivity in two ways:
- Use the or (|) symbol to search for multiple things. In the example above, if you are also searching for "Dog," you can change the formula to
=REGEXMATCH(B2, "dog|Dog"). - Use the lower function to convert your text to lowercase. In the example above, you can change your formula to
=REGEXMATCH(lower(B2), "dog").
Method Two: If Cell Contains Using IF & SEARCH
You can check your data if a certain cell contains a value using a combination of the IF and SEARCH functions is another way. You will combine them using the syntax:
=IF(SEARCH("text", B2) > 0, 1, 0)
When you use this formula, the cell (B2 in this case) that you are searching for gets evaluated to check if it contains the "text." If it does, "1" will be returned, but if it does not, "value" will be returned.
Unlike the REGEXMATCH method, this method can search for numbers without using the or (|) symbol or converting the text to lowercase.
If you consider the example from the previous method, replacing text with “dog,” your results will look like this:
However, this method does not autofill other cells, you will need to copy and paste the formula manually, replacing the cell name each time.
Method Three: If Cell Contains Using COUNTIF
Another method to check if a cell contains a value is using the COUNTIF function. The syntax for this function is
=COUNTIF(B2,"*text*")
This formula searches cells; if the text is in that cell, it returns 1. If it is not contained in the cell, it returns 0.
The asterisks surrounding your text are crucial because they will search for the text anywhere in the cell instead of looking for an exact match in the whole cell.
This method is not case-sensitive but does not search numbers. If you have to search for a number, you need to convert it to text before evaluation.
Here is the expected result using this formula:
Conclusion
The methods in this article are efficient and give the same result. They also have pros and cons, but you should use the one that suits you.
You may also like to read:
Export Google Calendar To Excel
How to Add Background Image to Google Calendar Event