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.
For example:
- 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.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Use SEARCH Function to Search Multiple Values
Excel: How to Use LEFT Until Specific Character
Excel: How to Use MID Function to End of String
