{"id":4375,"date":"2023-02-22T15:15:49","date_gmt":"2023-02-22T15:15:49","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=4375"},"modified":"2024-05-24T14:50:20","modified_gmt":"2024-05-24T14:50:20","slug":"how-to-use-find-function-and-search-function-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-use-find-function-and-search-function-in-excel\/","title":{"rendered":"How to use FIND Function and SEARCH Function in Excel?"},"content":{"rendered":"

FIND Function and SEARCH Function in Excel are invaluable tools for locating specific text within your spreadsheets. Whether you’re searching for a single word or a substring, these functions offer precise ways to identify the position of text within a cell. Say goodbye to manual scanning and hello to efficient text searches with FIND Function and SEARCH Function in Excel. Whether you’re analyzing large datasets, cleaning up data, or extracting specific information, these functions provide the flexibility and accuracy you need. Embrace the power of FIND and SEARCH to streamline your text-based operations and enhance your productivity. With just a few simple formulas, you can quickly locate and extract the information you need, making your Excel experience smoother and more efficient.<\/p>\n

This Content Covers:<\/strong><\/p>\n

    \n
  1. Definition of FIND Function and Search Function.<\/strong><\/a><\/li>\n
  2. Syntax and Arguments of FIND and SEARCH Functions<\/strong><\/a><\/li>\n
  3. Excel FIND vs. Excel SEARCH<\/strong><\/a><\/li>\n<\/ol>\n

    3.1 Case-sensitive FIND vs. Case-Insensitive SEARCH<\/strong><\/p>\n

    3.2 Working with Wildcard Characters<\/strong><\/p>\n

      \n
    1. How to use FIND Function and SEARCH Function in Excel? (With Examples)<\/strong><\/a><\/li>\n<\/ol>\n

      1. Definition of FIND Function and Search Function.<\/strong><\/h2>\n

      FIND Function: <\/strong>The FIND function in Excel is a built-in function that is used to extract the numerical location of one text string inside another. It is categorized as a String\/Text Function. The FIND function returns a #VALUE<\/strong> error if the text cannot be located. FIND function is a case-sensitive function.<\/p>\n

      SEARCH Function: <\/strong>Excel’s SEARCH function is quite similar to FIND since it also gives the position of a substring within a text string. Though it\u2019s similar to FIND function, the major difference between these two functions is that SEARthe CH function is case intensive, which means it can work with wildcard characters.<\/p>\n

      2. Syntax and Arguments of FIND and SEARCH Functions<\/strong><\/h2>\n

      Syntax:<\/strong><\/p>\n

      FIND-<\/strong> FIND( substring, string, [start_position] )<\/p>\n

      SEARCH- <\/strong>SEARCH(substring, string, [start_position]<\/p>\n

      Arguments: <\/strong>Both of these functions have the same arguments.<\/p>\n

      Substring-<\/strong> The substring to find.<\/p>\n

      String-<\/strong> The text to look up in.<\/p>\n

      Start position-<\/strong> [optional] specifies the character from which the search should start.<\/p>\n

      3. Excel FIND vs. Excel SEARCH<\/strong><\/h2>\n

      3.1 Case-sensitive FIND vs. Case-Insensitive SEARCH<\/strong><\/h3>\n

      Step 1: <\/strong>Let\u2019s look at the FIND<\/strong> function first, in the picture below we have a text string \u201cBamboo\u201d. Now I want to know the position of \u201cb\u201d inside the text string so I used FIND function to locate it\u2019s position. The function here returned 4 as we can clearly see the letter \u201cb\u201d is at the 4th<\/sup> position in this text.<\/p>\n

      \"FIND<\/p>\n

      Step 2: <\/strong>Now we will use the SEARCH<\/strong> function in cell B3 to locate the position of \u201cb\u201d. But the function here returned 1 because as the title of this particular section says, FIND function is case sensitive so it only looked for the lower case \u201cb\u201d inside the text string and ignored the upper case \u201cB\u201d. SEARCH function being case intensive didn\u2019t care about the case of the substring and located the position of the first \u201cb\u201d.<\/p>\n

      \"FIND<\/p>\n

      Step 3: <\/strong>If we use an upper case \u201cB\u201d as the substring then FIND function will also return 1 as you can see in the below given picture.<\/p>\n

      \"FIND<\/p>\n

      Step 4: <\/strong>Both the function returns #VALUE error if they are unable to locate the text.<\/p>\n

      \"FIND<\/p>\n

      3.2 Working with Wildcard Characters<\/strong><\/h3>\n

      In contrast to FIND, the Excel SEARCH function supports wildcard characters in the find text input.<\/p>\n

      Step 1:<\/strong> In the picture below, we have used a wildcard character inside the FIND formula. Though there is an \u201cx\u201d in the text string and a \u201c2019\u201d, the formula returned an error text.<\/p>\n

      \"FIND<\/p>\n

      Step 2: <\/strong>The text string referred to in the within text parameter contains both “x” and “2019”. So, the formula returns the location of the first character (“x”) in the substring, as seen in the picture below, regardless of how many characters are in between.<\/p>\n

      \"FIND<\/p>\n

      4. How to use FIND Function and SEARCH Function in Excel? (With Examples)<\/strong><\/h2>\n

      Now we will see how we can utilize FIND and SEARCH function through some examples.<\/p>\n

      Example 1:<\/strong> In this example we will learn how we can use FIND<\/strong> or SEARCH<\/strong> function with LEFT, RIGHT <\/strong>and LEN <\/strong>function to locate a text string preceding or following a given substring using this below given data.<\/p>\n

      \"FIND<\/p>\n

      Step 1: <\/strong>Select cell B2 and insert the below given formula. Here we have used the FIND function with LEFT function. The LEFT function in Excel returns the requested number of a string’s left-most characters. Additionally, you locate a space (” “) using the FIND function to tell the LEFT function how many characters to extract. In order to avoid having the space included in the returned value, you then deduct 1 from the space’s location.<\/p>\n

      =LEFT(A2,FIND(” “,A2)-1)<\/strong><\/p>\n

      \"FIND<\/p>\n

      Step 2:<\/strong> Now use the same formula in B3 but use SEARCH instead of FIND function this time.<\/p>\n

      \"FIND<\/p>\n

      Step 3: <\/strong>To extract the last name from the text string we have to use FIND<\/strong> or SEARCH <\/strong>function with RIGHT <\/strong>and LEN<\/strong> function. The LEN function here is needed to get the total number of characters inside the text string from which you subtract the position of the space.<\/p>\n

      \"FIND<\/p>\n

      Step 4: <\/strong>Now use the same formula with SEARCH function in C3.<\/p>\n

      \"FIND<\/p>\n

      Example 2: <\/strong>In this\u00a0situation\u00a0column A has a lengthy text string and you want to locate and extract only the content wrapped in (parentheses).<\/p>\n

      \"FIND<\/p>\n

      Step 1: <\/strong>To do this you would require the Excel FIND or SEARCH function to decide where to start and how many characters to extract, as well as the MID function to retrieve the desired number of characters from a string.<\/p>\n

      \"FIND<\/p>\n

      Step 2: <\/strong>Now let\u2019s use the same formula in B3 with SEARCH function.<\/p>\n

      \"FIND<\/p>\n

      Example 3: <\/strong>Suppose you want to locate the third dash (-) in a string of text from the below picture.<\/p>\n

      \"FIND<\/p>\n

      Step 1: <\/strong>Insert the following formula in cell B2 and hit Enter key.<\/p>\n

      \"FIND<\/p>\n

      Step 2: <\/strong>Now let\u2019s locate the position of the second dash (-) from the text string of cell A3 using SEARCH function. This formula’s first two parameters seek out a dash (“-“) in cell A2. Another FIND function is included in the third input (start num), instructing Excel to begin looking after the character that follows the initial dash (SEARCH(“-“,A2)+1).<\/p>\n

      \"FIND<\/p>\n

      Application of FIND Function and SEARCH Function in Excel<\/h2>\n