Excel find() function
The find() is a built-in method of Excel to find a text string into another string. As a resultant value, it returns the position of the first string where it finds in the second string. If it does not get the first string available inside another string, it returns #VALUE! error.
The find() function is a case-sensitive function and does not support wildcard operators. If this function found more than one occurrence of the string, it returns the position for the first occurrence. For example, for the substring “t”, inside “tutoraspire”, it will return 5 (first occurrence of t).
This chapter will cover each parameter for this function and describe it in detail.
Purpose
The main purpose of using this function is to find a string inside another string and get its position.
This function works same as the Excel prebuilt FIND option that is used to search something (number, string) in an Excel file. But it just navigates you to the text when found.
Return Value
The find() function returns the position of the first string where it is found inside the second string.
If it does not find the string, it returns an error called #VALUE!.
Syntax
The find() function takes mainly two arguments. Syntax with two mandatory parameters will be –
Additionally, Excel also allows to set a position to start searching of the string from a specific position. For this, the syntax will be –
Parameters
- Substring – Contains the text to be searched inside another search string. You can call it substring.
- String – Contains the text string within which the first string (substring) will be searched.
- [startfrom] – It contains an integer number. You can specify a number value to start the search from that position in the second (within_text) string.
Points about find() function
Below is some essential information about the find() function that you should know while working with it.
- The find() function does not support the wildcard operator.
- This function is case-sensitive. Hence, it treats the strings “Happy” and “HAPPY” differently.
- It starts counting from 1, not from 0. For example, for (“p”, “Empty”) – return 3.
- It returns a numeric value that is the position found of the first string in the second string.
- If this function found more than one occurrence of the string, it returns the position for the first occurrence.
- It returns 1 if no substring is passed to search.
- In MS Excel, find() function is putted into String/text function category.
- It works same as the Excel prebuilt FIND option.
Examples
There is a list of examples containing results for different expressions.
| Expression | Returned position | Explanation |
|---|---|---|
| =FIND(“v”, “tutoraspire”) | 3 | Position of v inside tutoraspire string is returned. |
| =FIND(“K”, “tutoraspire”) | #VALUE! | Return an error as it does not find the substring K inside the string tutoraspire. |
| =FIND(“”, “tutoraspire”) | 1 | Returns 1 because no substring is provided for search. |
| =FIND(“a”, “tutoraspire”) | 2 | Returned the first occurrence of a in the second string. |
| =FIND(“a”, “tutoraspire”) | 4 | Returned 6 because find() function is case sensitive. |
| =FIND(“a”, “tutoraspire”, 5) | #VALUE! | Start searching at position 5 in the second string and do not substring a. |
| =ISNUMBER(FIND(“He”, “World Hello”)) | TRUE | Returned TRUE by on finding substring match. |
| =ISNUMBER(FIND(“He”, “Welcome”)) | FALSE | Returned FASLE on not finding the substring inside the main string. |
You can implement these expressions in an Excel worksheet and verify that they are returning the same result.
Example 1
Let’s take an Excel worksheet containing some string or numeric data inside it. We will apply the find() function to search the substring inside another string stored in this Excel sheet.
Steps to implement find() function on Excel data
Step 1: We have taken some data in an Excel worksheet where column A is holding the searching string and Column B with which string will be searched.
When substring is present in string
Step 2: Perform the first finds operation on first row data and write the following formula in C2 cell.
=FIND(A2, B2)
Step 3: See that the find() function returned 3 as it find the vat substring at position 3rd inside tutoraspire string.
When substring is not present in string
Step 4: Now, check for others for the next substring. So, modify the above formula like this and write in C3 cell.
=FIND(A3, B3)
Step 5: It has now returned an error called #VALUE! as find() function does not found k inside tutoraspire string.
When substring is not provided
In case, if the user does not any substring to search, let’s see what it returns.
Step 6: Write the following formula for row 4 where A4 cell does not containing any substring to search.
=FIND(A4, B4)
Step 7: This time it has returned 1. Excel find() function always returns 1 if the user don’t pass any substring to be searched.
Step 8: Similarly, apply the find() function on other data and see the returned positions corresponding to each comparison.
We have also added remarks for each result.
Example 3: Provide [startFrom] parameter
All the examples that you have learned above are solved without specifying the search start [startFrom] parameter. Let’s see an example, how the find() function works if we use third (optional) [startFrom] parameter.
Step 1: We have taken the following dataset along with startFrom parameter to start the search from a specified position.
Step 2: Write the following formula for row 3 data, in which the find() function will start searching at position 5.
=FIND(A2, B2, C2)
Step 3: It did not find the substring : come inside the String : Welcome to Tutor Aspire after start searching at position 5.
Thus, it returned #VALUE! error.
Step 4: Now, perform the same operation on other cells data and use the following formula:
=FIND(A3, B3, C3)
Step 5: This time it found the specified substring inside the main string even after start searching at position 7 and returned position 9.
Example 3: find() with ISNUMBER()
This time we want results as either TRUE or FALSE for a substring inside another string. We will use the find() function nested inside ISNUMBER() function as get result as TRUE or FALSE. Use the functions as described to get the result as a Boolean value rather than position.
Syntax
=ISNUMBER(FIND(substring, String))
Return Value
- TRUE – It will return TRUE on finding the substring inside the string.
- FALSE – It will return FALSE if not find substring inside the string.
We have an example in which we will use the find() function inside ISNUMBER() to get the result as a Boolean value (either true or false).
Step to find string and get result as Boolean value
Step 1: We will use the same data used in first example that was containing the substrings and string on which we will perform this modified operation.
We have added one more column named Boolean Result to store the Boolean result.
Step 2: Write the formula as following –
=ISNUMBER(FIND(A2, B2))
Step 3: It found the substring (A2) inside string (B2). Hence, it returned TRUE.
Step 4: Now, we have applied the same formula on all the remaining data and get the result for them.
Look at the remark for each operation so that you can understand it better.
Difference between find() and search()
You are wondering that why not use the search() function instead of find(). Just like the find() function, search() function is also used to find a string inside another string and returns the position of searched string. Also, they have some differences.
There are some differences between them that is why they are used in different circumstances.
- The find() is a case-sensitive while search() is a case-insensitive function of Excel.
- The find() function does not support wildcard operation whereas search() function provides support to wildcard operators.
