Here I’m looking for “Hello”, which incidentally happens to occur in the very first position. As a result, the function returns 1 as the output when I input the formula =Search(“Hello”,”Hello, there!”). As you can see from this example, the SEARCH function identifies the position where the substring starts: the word “Hello” occupies the first 5 characters in the text, but the function tells you where it starts – the very first character in the string. Show
Row 3The second function finds the comma (,) in the third position of the main string. The formula here uses cell referencing instead of typing the text directly: =Search(B3,A3). Row 4The third function is quite interesting. Here I am looking for an empty space within the main string. However, instead of leaving out the third optional parameter, I chose to enter 10 as the starting_at parameter. As you can see, the function ignored the first two occurrences of the space character (at 5 and 9) and returned the next immediate occurrence after the 10th position. The formula I typed here is =Search(” “,A4,10). Row 5The fourth example demonstrates the capability of the SEARCH function to ignore the case (upper or lower) of the text. This is the difference between this function and the FIND function. Row 6The last example shows that even if the main text is alpha-numeric in nature, the function still works. What if the substring does not occur at all in the text? The function returns an error, as illustrated below: Readers like you help support MUO. When you make a purchase using links on our site, we may earn an affiliate commission. Read More.As a spreadsheet user, you may often see yourself sifting through data rows to find a specific keyword. On smaller spreadsheets, doing this manually is easy. However, with large spreadsheets, this becomes close to impossible. Thankfully, Google Sheets has several built-in functions that allow you to easily search for data in your spreadsheets. 4 Ways to Search in Google SheetsThere are multiple ways that you can search text in your spreadsheet. We will discuss some of them extensively here. Google Sheets allows you to use formulas and some built-in functions to search text in your spreadsheets. Here are some methods you can use to search text in your spreadsheet. The ways to search in Google Sheets we’ll cover in this guide are:
Each performs slightly differently, so checking them all out will help you find the perfect way to search Google Sheets depending on your specific needs. Whether you're just starting out on spreadsheets or are an experienced entrepreneur, learning to search is a very important skill for Google Sheets. 1. The Find and Replace ToolThis feature is built into Google Sheets and looks through the spreadsheet to find the keyword you provide. It provides a few advantages over using formulas to achieve the same purpose, the most prominent of which is case matching. It is also one of the best basic tools in Google Sheets. To access the Find and Replace feature in Google Sheets:
Alternately, you can use the Ctrl + H keyboard shortcut on Windows to do the same thing. On macOS, the shortcut is Cmd + Shift + H. In the above example, we can find the cells that contain the word "Pencil". In the Find and Replace window, you will see two text boxes: Find and Replace with. In the Find text box, enter your keyword. If you have multiple sheets, choose All sheets in the Search option. Finally, click on the Find button. This will take you to the first instance of the text in the spreadsheet. You can repeatedly press the button to traverse the spreadsheet to find the keyword. Alternatively, if you don't plan to replace any data, and you don't need any of the advanced search features, then use the Find feature instead. You can access this by using the Ctrl + F shortcut in Windows. For macOS users, the shortcut is Cmd + F. 2. The FIND FunctionYou can use this formula to find the position where the string is first detected in the text. This formula is case-sensitive, so ensure the text you enter the formula has the proper case. Here is the syntax for the formula: The formula uses three arguments. These are:
In this example, we have a sentence in cell A2. We wish to find the word fox there. Here are the steps to do so:
One thing to note is that this formula is case-sensitive. So if you write “Fox” instead of “fox” then the formula won't work. If you're looking for a similar function that isn't case-sensitive, you can use the SEARCH formula. Note: If you’re confused by the numerical result in the example, remember the FIND function notes the location of the string, in our case, fox. You’ll notice that the f in fox is the 17 character in the cell (including spaces) hence the result of 17. 3. The SEARCH FunctionThe SEARCH formula is almost identical to the FIND formula. Here is the syntax for the formula: As you can see, the SEARCH formula uses the same parameters as the FIND formula. We will use a different case for the search text to demonstrate this formula. Here are the steps to use the SEARCH formula.
4. The MATCH FunctionThe MATCH function outputs the position of a value in a defined cell range that matches a value specified by the user. Here is the syntax for the formula: The formula uses three parameters. These are:
In the type parameter, you can choose to enter either 1, 0, or -1.
Here are the steps you need to follow to use a MATCH formula:
The result is 1 as the first instance of “Desk” is in the first row of the range. Keeping Searching for Ways to Improve Your Spreadsheet SkillsAside from SEARCH and FIND, these functions work quite differently and are best used in different scenarios. Familiarize yourself with all of them for the best results in your spreadsheets. Mastering as many Google Sheets functions as possible will have you using the software like a pro in no time. How do I search for specific text in Google Sheets?Use the keyboard shortcut Control + F (for Windows) and Cmd + F (for Mac). This will open a small Find box at the top right part of your sheet. Enter the string that you want to search in the entire worksheet.
How do I search data in Google Sheets?Search for data. In Sheets, open a spreadsheet and click Edit. ... . Next to Find, enter the text or numbers that you want to find.. Next to Replace with, enter the new data.. Next to Search, choose the sheets that you want to search.. (Optional) To refine your search, select additional options.. How do you find a word in a text on Google?Tip: You can also use the keyboard shortcuts Ctrl + f (Windows, Linux, and ChromeOS) or + f (Mac) to find a word or phrase quickly. You can find more info about a specific word or phrase on a page. Highlight a word or phrase.
How do you filter words in Google Sheets?Filter your data. On your computer, open a spreadsheet in Google Sheets.. Select a range of cells.. Click Data. Create a filter.. To see filter options, go to the top of the range and click Filter . Filter by condition: Choose conditions or write your own. ... . To turn the filter off, click Data. Remove filter.. |