In this tutorial, we will learn about Excel FIND function. Excel FIND function seeks the location of a character in a string. And returns its position in the string from the starting character.
How to define Excel FIND function
“Excel FIND function seeks the position of a character or sub-string within a given string or text in a cell.”
Excel FIND Function
=FIND (find_text, within_text, [start_num])
find_text = text or a character we are looking for
within_text = this is the string in which we will find our desired text or a character
[start_num] = (Optional) this is an optional argument which tells the function to start to find character after the given position. If this is omitted then it will start counting from the beginning.
Things to remember
Excel FIND function will return #VALUE! Error when “find_text” is not appearing in the given string or text.
Excel FIND function will return #VALUE! Error if “start_num” is “0” or a negative value.
This function will not work in case of “wildcard characters”. Use Excel SEARCH function in that case.
This function will return #VALUE! Error when the value of “start_num” is greater than the total length of the given string or text.
Excel FIND function is case-sensitive; i.e. it will not treat UPPER (F) and LOWER (f) characters as equal. Excel FIND function treats both characters differently.
Here is the function
=FIND (find this character, within this text string, [start counting after this number])
In this example, we can see that we are looking for character “a” which is not appearing in the string “Excelnsg”. So, in this case, it gives the result as #VALUE!
In this example, we are finding for character “e”, which is appearing two times in the string “website”. According to rule if start_num is not given, it will start counting from the first character. If start_num is given then it will start counting from the specified start_num. See example in image:-
In this example, if the character we are looking for is appearing in the string. But start_num is given as “0” then it will return a #VALUE! result.
Here is a string “Very very good website”, where sub-string “very” is appearing two times. One start with a capital letter (Very) and second with a small letter (very). As we know that FIND is a case-sensitive function. When we apply the function of finding for sub-string we have to take a note of capital and small letter. See below image example:-
Here we can see that the value of start_num is greater than the length of given string. So, in this case, it will return a result as #VALUE!
This function takes a big role to separate or split text string before a special character. See example:–