The FIND and FINDB functions in Excel are powerful tools used to locate one text string within another, distinguishing between uppercase and lowercase letters. They are particularly useful for data manipulation and text analysis, allowing users to identify the position of a substring within a larger string. This functionality can streamline processes that require text parsing or validation.

Syntax

FIND(find_text, within_text, [start_num])

• find_text: The text you want to find within another string.
• within_text: The string in which to search for the specified find_text.
• [start_num]: Optional. Specifies the character position in within_text to start the search. Defaults to 1 if omitted.

Example #1

=FIND("s", "Excel functions")

This function locates the position of the letter “s” in the text “Excel functions.” The result is 12 since “s” is the 12th character.

Example #2

=FIND("z", "Excel functions")

Here, the function looks for the letter “z” in the same text. Since “z” does not exist, the result is an error: VALUE!.

Example #3

=FIND("c", "data analysis", 5)

This example searches for the letter “c” in “data analysis” starting from the 5th character. The result is 9 because “c” is found at the 9th position in the substring starting from the specified position.

Error handling

• VALUE!: This error occurs when the find_text is not found within the within_text.
• NUM!: This error is shown when the start_num is less than 1 or greater than the length of within_text.

Conclusion

The FIND and FINDB functions are indispensable tools in Excel for locating substrings within a larger string, especially when case sensitivity is important. Understanding their syntax, along with potential errors, allows users to effectively utilize these functions in various data analysis and text manipulation tasks. By mastering these functions, Excel users can enhance their capabilities in handling textual data efficiently.