When it comes to Google Sheets string functions, the <\/span>FIND<\/span><\/a> formula is closely related. It also returns the position of a substring within a string. The only difference is that FIND is case-sensitive, while SEARCH is not.<\/span><\/p>\n Note: If you want to learn more about other Google Sheet functions, we have a post with <\/span>100+ Google Sheet Functions<\/span><\/a><\/p>\n [\/et_pb_text][et_pb_text module_id=”syntax” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.16″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SEARCH(search_for, text_to_search, [starting_at])<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text module_id=”use” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As covered in the syntax, this function needs both the string and substring. But the third parameter, [starting_at], is optional. You only need to use this if there are multiple occurrences of the same substring.<\/span><\/p>\n Let\u2019s go over the examples in the image below to get a better understanding of the SEARCH function.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/SEARCH-FORMULA-GOOGLE-SHEETS.png” alt=”SEARCH FORMULA GOOGLE SHEETS” title_text=”SEARCH FORMULA GOOGLE SHEETS” align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The formula searches for the string \u201cHello\u201d, which incidentally happens to occur in the very first position. As a result, the function returns <\/span>1<\/b>.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =Search(\u201cHello\u201d, \u201dHello, there!\u201d)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see from this example, the SEARCH function identifies the position where the substring starts. The word \u201cHello\u201d occupies the first 5 characters, but the function tells you where it starts.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The second function searches for \u201c,\u201d and finds it in the third position of the main string. This formula uses cell referencing instead including the text directly.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =Search(B3, A3)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This third function is quite interesting. Here I am looking for an empty space within the main string. However, instead of leaving the <\/span>starting_at<\/b> parameter empty, I gave it a value of 10.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =Search(” “, A4, 10)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n As you can see, the function ignored the first two occurrences of the space character (at positions 5 and 9) and returned the next immediate occurrence after the 10th character..<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The fourth example demonstrates how the SEARCH function finds partial matches. It also illustrates that being case insensitive is the main difference between this function and the FIND function.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SEARCH(“eat”, “THAT\u2019S GREAT NEWS!”)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The last example shows that even if the main text is alpha-numeric in nature, the function still works.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SEARCH(\u201c1\u201d, \u201cAlpha178625\u201d)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”action” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s explore some practical cases where the SEARCH function could come in handy, either by itself or in combination with other functions.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”conditional” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Use this function within a conditional formatting rule. If the formula returns a number the keyword was found, and you can give the cell containing it a specific format. This is useful for scanning through customer feedback or other text data.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =SEARCH(“keyword”, A2:A)<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n To set up the conditional format, open the <\/span>Format cells<\/b> dropdown, select <\/span>Custom formula is<\/b>, and input the formula. Adapt the keyword and range according to your needs.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”tag” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This formula tags entries as “Product X” if the keyword “product_x” is found in the cell. You could even nest several IF statements to apply several possible tags based on a number of keywords.<\/span><\/p>\n Amongst other things, this can be used to segment customer feedback or sales data based on product mentions or categories.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =IF(ISNUMBER(SEARCH(“product_x”, A2)), “Product X”, “Other”)<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”list” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This function filters the range A2:A100 to include only those entries that contain the substring “Keyword”. This can be useful to single out all values that contain a particular substring.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n =FILTER(A2:A100, ISNUMBER(SEARCH(“keyword”, A2:A100)))<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”error” _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If the substring doesn\u2019t occur in the text, the function returns a value error as shown below. To learn how to handle errors like this check out <\/span>How to use the IFERROR function in Google Sheets<\/span><\/a>.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/05\/SEARCH-FORMULA-GOOGLE-SHEETS-2.png” alt=”SEARCH FORMULA GOOGLE SHEETS 2″ title_text=”SEARCH FORMULA GOOGLE SHEETS 2″ align=”center” module_class=”sheetgo-post-no-shadow-img” _builder_version=”4.16″ width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.24.1″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The SEARCH function\u00a0 is a powerful tool that can greatly enhance your data management and analysis capabilities.<\/span><\/p>\n From highlighting important keywords in vast datasets to creating dynamic lists, SEARCH can be adapted to a wide array of tasks.<\/span><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" The Google Sheets SEARCH function returns the position of a substring within a text. It is the right tool if you need to find a specific word, letter, or number.\u00a0 When it comes to Google Sheets string functions, the FIND formula is closely related. It also returns the position of a substring within a string. […]<\/p>\n","protected":false},"author":40,"featured_media":29199,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Spreadsheets applications are fantastic utilities at our disposal that not only allows us to tinker with numbers but also with text strings. So along the way, we may find ourselves needing to obtain the position of a substring within a string. We can do that with the help of the SEARCH<\/a> formula in Google Sheets.\n\nWe have a close sibling to this formula called FIND<\/a>. This too returns the position of a substring within a string. The only difference being that the FIND formula is case-sensitive, while SEARCH is not.\nSyntax<\/h2>\n
\n
How to use SEARCH Google Sheets<\/h3>\n
Example 1<\/span><\/h3>\n
Example 2<\/span><\/h3>\n
Example 3<\/span><\/h3>\n
Example 4<\/span><\/h3>\n
Example 5<\/span><\/h3>\n
SEARCH function in action<\/span><\/h2>\n
SEARCH function for conditional formatting<\/span><\/h3>\n
Categorizing data with SEARCH<\/span><\/h3>\n
List all values with a specific substring<\/span><\/h3>\n
SEARCH function error<\/span><\/h2>\n
Final thoughts on SEARCH<\/h2>\n
Syntax<\/h3>\nSEARCH(search_for, text_to_search, [starting_at])<\/strong>\n
\n \t
Usage: SEARCH formula in Google Sheets<\/h3>\nGoing by the syntax, the formula needs both the string and substring, as expected. But the third parameter is an optional one, in case there are multiple occurrences of the same substring. Let us now reinforce our understanding of the formula with the help of a few practical examples.\n\n
![\"SEARCH](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/07\/SEARCH-formula-Illustration-Frame-3.png\")
\n\nWe can validate the outcomes of all the examples above by with a quick manual observation.\n\nIn the first example, we are looking for \"Hello\", which incidentally happens to occur in the very first position. Hence it returned 1 for output. The second formula finds the comma (,) in the third position of the main string.\n\nThe third formula is a little interesting. Here we are looking for an empty space within the main string. However, instead of leaving out the third optional parameter, we chose to go with 10. So, the formula ignored the first two occurrences of the space character (at 5 and 9) and returned the next immediate occurrence after 10th position.\n\nThe fourth example demonstrates the capability of the SEARCH formula to ignore the case. Even if the main text is alpha-numeric in nature, the formula still works as shown in the final example.\n\nWhat if there is no occurrence of a substring at all? It returns an error as illustrated in the snapshot below.\n\n![\"SEARCH](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2018\/07\/SEARCH-formula-Illustration-Frame-4.png\")
","_et_gb_content_width":"","footnotes":""},"categories":[54],"tags":[39,28],"class_list":["post-9192","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-formulas","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/9192","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/40"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=9192"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/9192\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/29199"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=9192"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=9192"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=9192"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}