Both of MS Excel’s FIND and SEARCH functions are immensely useful. Both of these functions carry out comparable tasks, but they also differ in a few ways. Today’s tutorial will teach us about both of these functions and how they work.
This Content Covers:
- Definition of FIND Function and Search Function.
- Syntax and Arguments of FIND and SEARCH Functions
- Excel FIND vs. Excel SEARCH
3.1 Case-sensitive FIND vs. Case-Insensitive SEARCH
3.2 Working with Wildcard Characters
- Definition of FIND Function and Search Function.
FIND Function: 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 error if the text cannot be located. FIND function is a case-sensitive function.
SEARCH Function: Excel’s SEARCH function is quite similar to FIND since it also gives the position of a substring within a text string. Though it’s 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.
- Syntax and Arguments of FIND and SEARCH Functions
Syntax:
FIND- FIND( substring, string, [start_position] )
SEARCH- SEARCH(substring, string, [start_position]
Arguments: Both of these functions have the same arguments.
Substring- The substring to find.
String- The text to look up in.
Start position- [optional] specifies the character from which the search should start.
- Excel FIND vs. Excel SEARCH
3.1 Case-sensitive FIND vs. Case-Insensitive SEARCH
Step 1: Let’s look at the FIND function first, in the picture below we have a text string “Bamboo”. Now I want to know the position of “b” inside the text string so I used FIND function to locate it’s position. The function here returned 4 as we can clearly see the letter “b” is at the 4th position in this text.
Step 2: Now we will use the SEARCH function in cell B3 to locate the position of “b”. 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 “b” inside the text string and ignored the upper case “B”. SEARCH function being case intensive didn’t care about the case of the substring and located the position of the first “b”.
Step 3: If we use an upper case “B” as the substring then FIND function will also return 1 as you can see in the below given picture.
Step 4: Both the function returns #VALUE error if they are unable to locate the text.
3.2 Working with Wildcard Characters
In contrast to FIND, the Excel SEARCH function supports wildcard characters in the find text input.
Step 1: In the picture below, we have used a wildcard character inside the FIND formula. Though there is an “x” in the text string and a “2019”, the formula returned an error text.
Step 2: 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.
- How to use FIND Function and SEARCH Function in Excel? (With Examples)
Now we will see how we can utilize FIND and SEARCH function through some examples.
Example 1: In this example we will learn how we can use FIND or SEARCH function with LEFT, RIGHT and LEN function to locate a text string preceding or following a given substring using this below given data.
Step 1: 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.
=LEFT(A2,FIND(” “,A2)-1)
Step 2: Now use the same formula in B3 but use SEARCH instead of FIND function this time.
Step 3: To extract the last name from the text string we have to use FIND or SEARCH function with RIGHT and LEN 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.
Step 4: Now use the same formula with SEARCH function in C3.
Example 2: In this situation column A has a lengthy text string and you want to locate and extract only the content wrapped in (parentheses).
Step 1: 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.
Step 2: Now let’s use the same formula in B3 with SEARCH function.
Example 3: Suppose you want to locate the third dash (-) in a string of text from the below picture.
Step 1: Insert the following formula in cell B2 and hit Enter key.
Step 2: Now let’s 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).
For ready-to-use Dashboard Templates:
