What does it do?
The Excel FIND function allows you to find the starting position of a text string within another text string. The text string to find is case sensitive, it can be one character in length, a word or words, but you may NOT use wildcard characters. If found, it will return a whole number; this number represents the starting position of the text you attempted to find, reading from left to right, if not found, it will return #VALUE!.
Syntax Explained
Find_text: This is the text you are looking for.
Within_text: This is the text string that find_text, may or may not appear in.
Start_num (optional): This is the position where the search should begin, if omitted, if will default to 1. The 1 represents the first character in the within_text text string.
Examples
1. FIND covering many types.
In the example above I have set up a few text strings to find:
- Row 3 = Searching for a £ sign, returns a 12, note that spaces count as a character.
- Row 4 = Searching for a + sign, returns a 1, since this is the first character.
- Row 5 = Searching for a word where casing does not match, therefore returns a #VALUE! return. Check out the IFERROR function to allow for a descriptive message when a #VALUE! result is returned. If you want to find and ignore case, use the SEARCH function.
- Row 6 = As above, but all as lower case, now returns a 5.
Pro Tip
Combining, or using alternatives to the FIND functions such as SEARCH, MID, LEN, RIGHT and LEFT can help support text extraction techniques. When used correctly they can make light work of something that would take hours/days using manual methods.