the other thing i sd mention is that i also need to separate the LEFT
part; thus i need some uniformity between the 2 formulas as they need to
do the same thing--look 4 the same words--but extract the opposite
portion of the text (right versus left).
how wd u propose the same formula for the left part "ABC" in "ABCcompany"?
just changing RIGHT to LEFT does not work.

Ron Coderre wrote:
If the source cell will always end in one of the words you are looking for,
then maybe this will help:

For a phrase in A1

B1:
=RIGHT(A1,SUM(COUNTIF(A1,{"*Company","*Firm","*Cor poration","*Partnership"})*{7,4,11,11}))


Is that something you can work with? or do you need something else?
***********
Regards,
Ron

XL2002, WinXP


"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!