You can learn here an awesome trick using IF, the popular Logical Test function in Google Sheets. With this tutorial titled how to use IF Function to test a sentence for a text, I am intending to explain two important usages of IF formula in Google Sheets. What are they?
1. How to Use IF as an Array Formula.
2. How to test a column containing the comma separated words, sentences, phrases etc. to find a specific word.
Similar:Â Vertical Lookup in a Column for a Word in Google Sheets
In Google Sheets, How to Use IF Function to Test a Sentence for a Text that Contains
First, let’s start with a sentence in a single cell. In Cell A1, I have the following sentence. “Ms. Tricia Reeves is coming from Los Angeles. Pick up arranged.”
In Cell B2, I want a formula to test this sentence in A1 for a word that says “Los Angeles”.
As you know, we can’t use a basic IF Function to find this since a normal IF function can only check whole words. So what is the solution?
Steps to Find A Word In A Sentence Using IF Logical Function in Google Sheets.
1. You can use either a FIND formula or SEARCH Formula to find the position of the text in question, i.e., “Los Angeles”. If the search Word is available, the formula would return the position of the string (a numeric value) else a “#Value!” error.
Which Formula to Use. Find or Search?
You can use either of the formulas above. Both of them can return the position of the search string. The only difference is, the FIND function is case sensitive but the SEARCH is not. Below is the formula.
=find("Los Angeles",A2:A,1)
2. Now you can make use of the above formula to do a logical test.
If the above FIND formula returns a numerical value, it means the search word is present in the sentence and you can use the IF logical test formula as below.
=if(find("Los Angeles",A1:A,1)>0,"LA","")
This IF logical test would return “LA” if the word “Los Angeles” exists in the sentence. But the FIND formula may return “#Value!” error if the text is not available. So you should use an IFERROR formula to avoid this.
=iferror(if(find("Los Angeles",A2:A,1)>0,"LA",""))
With this formula, we can test a sentence in a cell, for a word that contains. But when you want to test an entire column as per the screenshot on the top of this page, you should use an Array IF formula.
=ArrayFormula(iferror(if(find("Los Angeles",A2:A,1)>0,"LA","")))
Conclusion:
This way, you can use IF function to test a sentence for a text in Google Sheets. You can nest the above IF formula to search multiple texts. Below is the link to my sample sheet containing the above Google Sheets experiment.
Google Sheets IF Test in Sentence
Note: If you want my personal assistance to sort out your spreadsheet problems, you can contact me. Please check my contact page for more info. Thanks for the drop by.
