A to Z of Excel Functions: The FINDB Function

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

The FINDB 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 FINDB function employs the following syntax to operate:

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

• this function is not available in all languages
• FINDB is intended for use with languages that use the double-byte character set (DBCS)
• FINDB always counts each double-byte character as 2 when you have enabled the editing of a language that supports DBCS and then set it as the default language.  Otherwise, FINDB counts each character as 1
• The languages that support DBCS include Chinese (Simplified), Chinese (Traditional), Japanese and Korean
• FINDB 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 SEARCHB instead
• if find_text is "" (empty text), FINDB 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, FINDB returns the #VALUE! error value
• if start_number is not greater than zero (0), FINDB returns the #VALUE! error value
• if start_number is greater than the length of within_text, FINDB returns the #VALUE! error value
• use start_number to skip a specified number of characters.  Using FIND 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.

In the following example, FINDB returns 3 because each character is counted by its bytes; the first character has 2 bytes, so the second character begins at byte 3.

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.