Are you looking for a powerful tool to help you find specific words or combinations of letters within a text in Google Sheets? Look no further! In this article, we’ll explore the RECHERCHE function in Google Sheets and how it can make your life easier when it comes to locating specific words or phrases in text strings.
How to Use the RECHERCHE Function in Google Sheets
Using the RECHERCHE function is simple. Just follow these steps:
- Type “=RECHERCHE” or go to “Insert” → “Function” (or access the “Functions” icon) → “Text” → “RECHERCHE”.
- Specify the word or phrase you want to search for and the text where the function will search for it. You can also indicate the starting point of the search if necessary.
- Press Enter.
The general syntax of the formula is as follows:
- “Rechercher”: The word or phrase you want to find.
- “Texte_à_rechercher”: The text where the function will search for the specified keyword.
- “Démarrer_à [Optional]”: This argument determines the starting point of the search in the specified text. If no value is provided, the function assumes it’s 1 (the first letter of the text string).
It’s important to note that if the function fails to find a match, it will return “#VALEUR” (which means “#VALUE” in English).
Let’s look at some examples to understand how this formula works in Google Sheets.
-
The first two examples: The difference between the first two examples lies in the optional argument “Démarrer_à”. In the second formula, with 6 in the “démarrer_à” parameter, the search for the word starts from the sixth position, at the end of the word “J’aime”. Note that the RECHERCHE formula counts the position number where the specified word appears from the beginning of the text, regardless of the “démarrer_à” argument.
-
The third and fourth examples: The RECHERCHE function returns “#VALEUR” in the third and fourth examples. In the third example, with 8 in the “démarrer_à” parameter, the function starts searching for “neuf” from the letter “e” (at the end of the word “gentil”), which is positioned at the tenth place in the text. However, after “e”, there is no matching group of letters for “neuf”, so the function returns “#VALEUR”. Similarly, the formula fails to find “lui” in the specified text of “Je l’ai vu” and returns “#VALEUR” here.
-
The fifth example: The last example showcases one of the features of this function. Since “lui” and “LUI” differ in case sensitivity, the RECHERCHE formula, which is case-sensitive, returns “#VALEUR”.
You have now learned how the RECHERCHE formula works in Google Sheets. We’ll explain another example where we combine the RECHERCHE function with the SIERREUR and SI formulas to check if a specific word is included in a selected text. Let’s assume we want to check if each text contains the word “avoir” or not.
The top table describes the two steps you need to follow.
The first step is to use the RECHERCHE formula to check if “avoir” is included in each text. In the “Result” column, we use the SIERREUR formula to assign a different value, “Non” (which means “No” in English), to “#VALEUR” when there is no match in a search performed by the RECHERCHE function.
The second step is to provide the answers to the question. In the “Include ‘avoir’?” column, we apply the SI function to display “Non” when the value returned by the SIERREUR formula is “Non”. Otherwise, we provide “Oui” (which means “Yes” in English) when the SIERREUR formula gives us a numeric value.
The bottom table shows the same results in one step where we use SI formulas containing two functions – the RECHERCHE function and the SIERREUR function.
These formulas serve as examples. Feel free to find the best combination of formulas that suits your specific goals.
For more tips and tricks on Google Sheets, visit Crawlan.com. Happy exploring!
