find for any of some search texts within a text

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.

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


"xirx" <xirx@gmx.de> wrote in message
news:42b0461a$0$298$4d4ebb8e@read.news.de.uu.net...
> 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.

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?
--
Regards,
Ron