Excel Find Function

Find Function

Find function is used to fetch the position of a sub string in a string of characters. Sub string can be a single character or a group of characters existing in a string. It is a built-in, string/ text function in excel and can be used as a worksheet function.

Syntax:

=FIND(sub string, string, start_position) or =FIND(find_text, within_text, start_position)

Purpose:

To fetch the location of a text in a string

Return Value:

A number representing the location of sub string

Arguments:

• Sub string: The text or a cell containing text that you want to find
• String: The text string to search within. Usually it is a cell reference but string can also be typed in the formula
• Start_position: An argument that specifies from which character the search will begin. It is optional. If omitted, FIND will count from the first character of the string.

*Remember start_position only specifies the search start position. Result will be counted from the starting of the string.
For example: In a string of 9 characters, you need to fetch the position of a character at 7th place. And you put 5 as the start_position.
In this case, FIND will search from the 5th character and position will be counted from the first character of the string. So, answer will be 7*

Excel FIND function - things to remember!

To get the desired result using excel Find function; keep the following points in mind:

• FIND function is case-sensitive, use SEARCH function for case-insensitive match
• FIND function does not allow using wildcard characters
• If sub string contains several characters, FIND returns the position of the first character in the sub string
• If string contains several occurrences of sub string, FIND returns the position of the first occurrence
• If sub string is an empty string means “”, FIND returns the position of the first character in the search string
• FIND function returns #VALUE! Error, when:

1. Sub string does not exist in the string
2. Start_position is more than the number of characters of the string
3. Start_position is zero or a negative value