How can I use FIND Function with Multiple Criteria in Excel?

---

You can use the FIND function in Excel to find the position of the first occurrence of a specific character in a string.

However, sometimes you may wish to use the FIND function to search for the first occurrence of one of several characters in a string.

You can use the following formula to do so:

=FIND("a",SUBSTITUTE(SUBSTITUTE(A2,"b","a"),"c","a"))

This particular formula searches cell A2 and returns the position of the first occurrence of any of the following characters:

• a
• b
• c

If none of these characters is found in cell A2, then the formula returns #VALUE! as a result.

The following example shows how to use this formula in practice.

Example: How to Use FIND Function with Multiple Criteria in Excel

Suppose we have the following list of basketball team names in Excel:

Now suppose we would like to find the position of the first occurrence of either an a, b, or c in each team name. 

We can type the following formula into cell B2 to do so:

=FIND("a",SUBSTITUTE(SUBSTITUTE(A2,"b","a"),"c","a"))

We can then click and drag this formula down to each remaining cell in column B:

Column B displays the position of the first occurrence of either an a, b, or c in each team name. 

• The first position of an a, b, or c in Mavericks is in position 2.
• The first position of an a, b, or c in Cavs is in position 2.
• The first position of an a, b, or c in Celtics is in position 6.

Note that the FIND function is case-sensitive.

How This Formula Works

Recall the formula that we used to find the position of the first occurrence of either an a, b, or c in cell A2:

=FIND("a",SUBSTITUTE(SUBSTITUTE(A2,"b","a"),"c","a"))

Here is how this formula works:

First, we use the SUBSTITUTE function to substitute the occurrence of each b in cell A2 with an a instead.

Then we use another SUBSTITUTE function to substitute the occurrence of each c in cell A2 with an a instead.

Lastly, we use the FIND function to simply find the position of the first occurrence of an a in cell A2, which is now equivalent to finding the position of the first occurrence of a, b, or c.

Excel: Use SEARCH Function to Search Multiple Values