Google Sheets has proven to be an expert tool when performing all kinds of tasks in your worksheet.
If you are working with a large dataset with multiple texts inside a column but you only want to extract a single text string from the main text string, you would need the SEARCH function.
The SEARCH function operates by extracting the position of a text from the main text string.
If you are worried about the text case of your data, the SEARCH function is not case sensitive and won’t affect the latter case of your texts.
In this tutorial, we are going to learn How To Use SEARCH Function In Google Sheets.
The Formula for SEARCH Function in Google Sheets.
The SEARCH function takes the syntax below.
=SEARCH(search_for, text_to_search,[starting_at])
Where;
- Search_for – This means the text you are looking for in the main text string.
- Text_to_search – This is the main sentence or text string where you want to search for a text.
- Starting_at – This is an optional parameter. It is denoted as one by default. It refers to the position where you want the SEARCH function to look for the search text. This parameter can also be included if there are multiple repetitions of a particular text.
How To Use the SEARCH Function?
Before we learn how the search function is used, there are a few properties of the function that you would have to take note of.
- The SEARCH function is not case sensitive. This means it doesn’t affect the text case of the texts in the main string.
- The FIND function is similar to the SEARCH function. The only difference between the two functions is that the FIND function is case sensitive.
- When using the SEARCH function, ensure that you don’t mix up the function’s parameters. This could cause an error on your worksheet.
- The SEARCH function works fine if there are wildcards in your main text string.
- The search_for value must not be more than the text_to_search value. If this occurs, a #VALUE! error message is returned.
- Also, note that the search_for value is present in the text_to_search value. If the search_for value is absent, a #VALUE! error is returned.
Now we’ve stated the key requirements and notes to take, let’s see how to use this function in a practical example.
In the sample worksheet, we have a list of statements in column A.
So we want to use the SEARCH function to find the position of the substrings from the main text strings.
Step 1: Enter the formula, =SEARCH(“Good”, “Good Morning”) in the result cell C2.
Step 2: Hit the Enter key and the function returns 1. The SEARCH function has located “Good” as the first word in the main text string.
Alternatively, you can place the formula’s parameters into cells so that you can reference them in the formula instead of entering them directly into the formula .
We’ve separated the two parameters into cells A3 and B3, each containing the Search_for value and text_to_search respectively.
So in this case, we reference the cells to the formula. We enter the formula into cell C3 as
=SEARCH(B3, A3).
Hit the Enter key and the formula returns a 17 as the location of “cake” in the main text string.
In a third instance, we want to prove that the SEARCH function is not case sensitive. We’ve entered the main text string in uppercase in cell A4 while the search substring is in lowercase in B4.
Using the cell reference method, we would just enter the formula as
=SEARCH(B4, A4)
Hit the Enter key on your keyboard and the formula returns the location of dogs as 11 in the main text string.
Lastly, the search function also works with statements in the alpha-numeric format. Like the text in cell A5.
Why Do We Use The SEARCH Function in Google Sheets?
The SEARCH function is used to extract important aspects of data from your worksheet. It is very useful when you want to know the position of particular texts when working with large datasets.
Final Thoughts.
That’s all you need to know how to use this function properly in Google Sheets. Be sure to take notes of the SEARCH function syntax and formulas work.
Now you know How To Use SEARCH Function In Google Sheets. Thanks for reading.
