Tuesday March 3rd, 2020

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.

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.

I will explain this with three easy examples.

- 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. - 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. - 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”.

Pascal Fabiani