How to find multiple texts with the FIND function.(Microsoft Excel)

Japanese version.

The FIND function, which searches for character positions, accepts only one string as "find_text".
Here is a method to search multiple "find_texts".

Steps

How to stack IFERROR functions.

The FIND function returns a #VALUE error if "find_text" is not found, which is detected by the IFERROR function, and if it is an error, the next FIND function is executed.

If more than one "find_text" match is found, the number of characters in the left-most "find_text" is used as the result of the formula.

=IFERROR(FIND("A",B3),IFERROR(FIND("D",B3),FIND("Z",B3)))
=IFERROR(FIND("Z",B4),IFERROR(FIND("D",B4),FIND("A",B4)))
=IFERROR(FIND("Z",B5),IFERROR(FIND("S",B5),FIND("A",B5)))
=IFERROR(FIND("Z",B6),IFERROR(FIND("U",B6),FIND("S",B6)))

Advantages

Unlike Array formula and Spill discussed below, the advantage is that the use of common functions makes the formulas easier to understand when reading them by others or after a period of time.

Disadvantages

Formulas become longer when there are more search strings. As a result, they are more difficult to read and change.

Using Array formula or Spill.

Array formula can be used to specify "find_text" in a range of cells without overlapping IFERROR function.

=MAX(IFERROR(FIND(find_text cell range, within_text),"-"))

If you press [Ctrl]+[Shift]+[Enter] in this state, the formula is enclosed in {} and becomes an Array formula.

If you type {} on the keyboard, it will not become an Array formula.

If an Array formula is used, the maximum result will be obtained with the MAX function after trying all theFIND function for the "find_text" specified in the cell range. The IFERROR function is also required to exclude #VALUE.

Using Array formula does not allow copy/paste, but auto-filling allows duplication.

In this case, the result is the start of the right-most hit string of the target.

In the case of the MIN function, the result is the start position of the left-most hit "find_text".

=MIN(IFERROR(FIND(find_text cell range, within_text),"-"))

In the version where the Spill is implemented, "find_text" is specified as a cell range.

The MIN function will produce the left-most result, and the MAX function will produce the right-most result.

=MIN(IFERROR(FIND(B3:B6,"ABCDE"),"-"))
=MAX(IFERROR(FIND(B3:B6,"ABCDE"),"-"))

Advantages

Even if "find_text" is increased, the formula remains short and easy to read because the cell range is only expanded.
The formulas remain short and easy to read.

Disadvantages

The disadvantage is that not many people are familiar with Array formula and Spill, so the meaning of the formulas is often not understood.

Also, if you mistakenly enter cell input mode and press ENTER normally, the Array formula will be canceled and only the first "find_text" in the cell range will be covered.

With Spill, the latter drawback does not exist.

---

Tips(Microsoft Excel)