Excel FIND Function (Example + Video)
When to use Excel FIND Function
Excel FIND function can be used when you want to locate a text string within another text string and find its position.
What it Returns
It returns a number that represents the starting position of the string you are finding in another string.
Syntax
=FIND(find_text, within_text, [start_num])
Input Arguments
- find_text – the text or string that you need to find.
- within_text – the text within which you want to find the find_text argument.
- [start_num] – a number that represents the position from which you want the search to begin. If you omit it, it starts from the beginning.
Additional Notes
- If the start number is not specified, then it starts looking from the beginning of the string.
- Excel FIND function is case-sensitive. If you want to do a case-insensitive search, use Excel SEARCH function.
- Excel FIND function cannot handle wildcard characters. If you want to use wildcard characters, use the Excel SEARCH function.
- It returns a #VALUE! error if the searched string is not found in the text.
Excel FIND Function – Examples
Here are four examples of using Excel FIND function:
Searching for a Word in a Text String (from the beginning)
In the above example, when you look for the word Good in the text Good Morning, it returns 1, which is the position of the starting point of the searched word.
Note that Excel FIND function is case-sensitive. When you use good instead of Good, it returns a #VALUE! error.
If you are looking for a case-insensitive search, use Excel SEARCH function.
Finding a Word in a Text String (with a specified beginning)
The third argument in the FIND function is the position within the text from where you want to start the search. In the example above, the function returns 1 when you search for the text Good in Good Morning and the starting position is 1.
However, it returns an error when you make it start at 2. Hence, it looks for the text Good in ood Morning. Since it can not find it, it returns an error.
Note: If you skip the last argument and don’t provide the starting position, by default it takes it as 1.
When there are Multiple Occurrence of the Searched Text
Excel FIND function starts looking in the specified text from the specified position. In the above example, when you look for the text Good in Good Good Morning with the starting position as 1, it returns 1, as it finds it at the beginning.
When you start the search from the second character onwards, it returns 6, as it finds the matching text at the sixth position.
Extracting Everything to the Left a Specified Character/String
Suppose you have the email ids do some superheroes as shown below and you want to extract only the username part (which would be the characters before the @).
Below is the formula that will find the position of ‘@’ in each email id and extract all the characters to the left of it:
=LEFT(A2,FIND(“@”,A2,1)-1)
The FIND function in this formula gives the position of the ‘@’ character. The LEFT function that uses this position to extract the username.
For example, in the case of brucewayne@batman.com, the FIND function returns 11. LEFT function then uses FIND(“@”,A2,1)-1 as the second argument to get the username.
Note that 1 is subtracted from the value returned by the FIND function as we want to exclude the @ from the result of the LEFT function.
Excel FIND Function – VIDEO
Related Excel Functions:
- Excel LOWER Function.
- Excel UPPER Function.
- Excel PROPER Function.
- Excel REPLACE Function.
- Excel SEARCH Function.
- Excel SUBSTITUTE Function.
You May Also Like the Following Tutorials:
