Excel Find Function

This Excel tutorial explains how to use Excel FIND Function for Excel worksheet, and also explains the differences between INSTR and SEARCH function.

You may also want to read:

Extract text in brackets

Remove text in brackets

Excel Find Function

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

Function Result Explanation
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!

 Outbound References

http://www.techonthenet.com/excel/formulas/find.php
https://www.youtube.com/watch?v=2R2OYJbKHlA

Leave a Reply

Your email address will not be published.