A to Z of Excel Functions: The FIND Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the FIND function.

The FIND function locates a text “sub-string” inside a longer text string, and returns the starting position of it within the parent string (i.e. where the first character is in the longer text string).  This function is not available in all languages.

The FIND function employs the following syntax to operate:

The FIND function has the following arguments:

• find_text: this is required and represents the text you wish to find
• within_text: this is also required. This represents the longer (parent) string that contains the text you seek
• start_number: this is optional.  This specifies the character at which to start the search.  The first character in within_text is character number 1.  If you omit start_number, then it is assumed to be 1.

It should be noted that:

• FIND is intended for use with languages that use the single-byte character set (SBCS)
• FIND always counts each character, whether single-byte or double-byte, as 1, no matter what the default language setting is
• FIND is case sensitive and doesn't allow wildcard characters.  If you don't want to do a case sensitive search or use wildcard characters, you can use SEARCH instead
• if find_text is "" (empty text), FIND matches the first character in the search string (that is, the character numbered start_number or 1)
• find_text cannot contain any wildcard characters
• if find_text does not appear in within_text, FIND returns the #VALUE! error value
• if start_number is not greater than zero (0), FIND returns the #VALUE! error value
• if start_number is greater than the length of within_text, FIND returns the #VALUE! error value
• use start_number to skip a specified number of characters.  As an example, suppose you are working with the text string "SumProduct".  To find the number of the first "u" in the descriptive part of the text string, set start_number equal to 4 so that the first part of the text is not searched.  FIND begins with character 4, finds find_text at the next character, and returns the number 8.  FIND always returns the number of characters from the start of within_text, counting the characters you skip if start_number is greater than 1.

Please see my example below: 

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.