this is where i am currently; and for the sake of not wasting more time,
i will probably end up using: *(replace G2 by A1)* ("w_list" is
obviously the list of terms appearing most often--i.e. types of companies.)

in B1:
=IF(ISERROR(LEFT(G2,LOOKUP(99^99,SEARCH(w_list,G2& "|"))-1)),G2,LEFT(G2,LOOKUP(10^99,SEARCH(w_list,G2&"|"))-1))

and in C1:

=IF(ISERROR(LOOKUP(99^99,SEARCH(w_list,G2),w_list) ),"",LOOKUP(99^99,SEARCH(w_list,G2),w_list))



stef wrote:
Excel 2002 SP3
Win XP HE SP1

*Follow-up to: microsoft.public.excel*


in cell A1, i have text string ABCcompany.
in cell B1, the formula: =RIGHT(A1,LEN(A1)-FIND("company",A1,1)+1)gives
result: company

i want to expand the formula to include "company" OR "firm" OR
"corporation" OR "partnership" all nested in the 1 formula so that if
any cells in column A contain any of the above words (not just
"company") it will return the corresponding result.
e.g. ABCfirm would return: firm ; ABCpartnership would return
partnership, etc.

tx!