Excel SEARCH functions: Part One: The FIND function

Excel provides a wide variety of text and numeric data processing tools. Two popular search functions are FIND and SEARCH. These are similar functions. First, I will discuss the FIND function.

The FIND function in Excel is used to return the position of a specific character or substring within a text string.

1 Syntax

= FIND(find_text, within_text, [start_num])

The breakdown of this is:

  • Find_text indicates the character or substring you want to find.
  • Within_text indicates the text string to be searched within. Usually it is supplied as a cell reference, but you can also type the string directly in the formula.
  • Start_num is an optional argument that specifies from which character the search shall begin. If omitted, the search starts from the 1st character of the within_text string.

If the FIND function does not find the find_text character(s), a #VALUE! error is returned.

2 Examples

I will explain this with three easy examples.

  1. Suppose you want to find the letter “O” in “OPERAHOUSE”.
    The formula for this is :
    = FIND(“O”; ”OPERAHOUSE”) or you can directly refer to the cell =FIND(“O”; A3).
    This formula returns 1 because “O” is the first letter in the word “OPERAHOUSE”. If you want to find the second “O” of this word, you have to adapt this formula to = FIND(“O”;”OPERAHOUSE”;2). This returns 7 as result.
  2. Suppose now that you want to find the small letter “o” instead of the big letter “O”.
    Using the formula =FIND(“o”; ”OPERAHOUSE”) or =FIND(“o”;A9) results in an error because the FIND function is case sensitive.
  3. Last, we want to check if there is an “F” in the word “OPERAHOUSE”.
    The formula =FIND(“F”;”OPERAHOUSE”) or =FIND(“F”;A12) returns an error because there is no “F” in “OPERAHOUSE”.

Figure 1: Examples in practice – FIND