Excel Find function is used to search a substring (part of the string) within a string and return the position of the first occurrence, note that Excel Find function is case sensitive. Excel Search function is very similar to Find function, but they are different in the way that Search function can use Wildcard and is case insensitive.
For example, in the string “FinanceDepartment”, the substring “Department” can be found at 8th position within “FinanceDepartment”, the function will return number “8”.
If we try to search substring “Division” within “FinanceDepartment”, since it cannot be found, error #VALUE! will return, but you can get rid of the error using IFERROR Function, I will show an example below.
For Excel VBA, please use Instr Function.
Syntax of Excel Find Function
Find( substring, string, [start_position] )
|substring||part of the string you want to search|
|string||the full string to be searched|
|[start_position]||Optional. It is the starting position for the search, default i s 1. For example, if you want to search “De” from “Development Department”, beginning from the 13th position, the function will return 13, not 1.|
Excel VBA does not have FIND or SEARCH function (only FIND Method for Range), but VBA Function INSTR is very similar to FIND, but they are different in the way that INSTR allows case sensitive or case insensitive comparison and return 0 if substring is not found. INSTR function also allows Wildcard but not Find function.
Example of Excel Find Function
|FIND(“2″,”123123”,3)||5||The first occurrence of “2” in “123123” starting from position 3 is at position 5|
|FIND(“f”,“FINANCE”)||#VALUE!||Since FIND Function is case sensitive, “f” cannot be found|
|FIND(“D”,“FINANCE”)||#VALUE!||“D” not found in substring|
|IFERROR(FIND(“D”,”FINANCE”),”error”)||error||Capture error to prevent error #VALUE!|