find for any of some search texts within a text

[xirx]

Let's say I am need to find the position of either "abc"
or "xyz" in the text in cell A1 (whatever occurs first).

An cumbersom way to do it is:

=IF(ISNUMBER(FIND("abc";A1));FIND("abc";A1);
IF(ISNUMBER(FIND("xyz";A1));FIND("xyz";A1);NA)

Something like

=MIN(FIND("abc";A1);FIND("xyz";A1))

would be much smarter. But if the search text in A1
does only contain either "abc" or "xyz" (not both),
one find will return #VALUE and unfortunately,
MIN returns #VALUE if one to the values is #VALUE.

If would be much better, if FIND would return
a value like #INFINITY if the search fails, whereas
X < #INFINITY for any number X.

Anyway. Is there any smarter way to handle this?
And of course, I am not only interested in searching
for two search string, but for "any" number of
search strings.

Any pointers welcome.

 

[Bernie Deitrick]

xirx,

Array enter (enter using Ctyl-Shift-Enter)

=MIN(IF(ISERROR(FIND({"abc","xyz"},A1)),"",FIND({"abc","xyz"},A1)))

Of course, you'll need to change the separators back to ; from ,

You can extend the list of items searched for more easily with this format
as well.

HTH,
Bernie
MS Excel MVP

 

[Ron Coderre]

Here's an option:

=SUMPRODUCT(MIN(IF(COUNTIF(A1,{"*abc*","*def*","*ghi*","*zzz*"}),FIND({"abc","def","ghi","zzz"},A1))))
Committed with just [Enter]

or

=MIN(IF(COUNTIF(A1,{"*abc*","*def*","*ghi*","*zzz*"}),FIND({"abc","def","ghi","zzz"},A1)))
Committed with [Ctrl]+[Shift]+[Enter]

Does that help?