How to use Excel FIND function with example

Excel FIND function

 

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])

 

Syntax

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])

 

Example 1

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!

 

Excel FIND Function

 

Example 2

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:-

 

Excel FIND Function

 

Example 3

 

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.

 

Excel FIND function

 

Example 4

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:-

 

Excel FIND function

 

Example 5

 

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!

 

Excel FIND function

 

 

Example 6

This function takes a big role to separate or split text string before a special character. See example:

 

Excel FIND function

 

 

Related Post

 

Excel LEFT FunctionExcel RIGHT FunctionExcel MID Function

 

2 thoughts on “How to use Excel FIND function with example”

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.