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!

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!

Ron,
tx for ur reply. it does work...however, i oversimplified the example in
my op.
i believe using FIND is better for me as s'times, the word "firm" for
example will be in the middle and not right, as in ABCfirmpartnership.
i do recognize the value or what u propose tho; it is helpful.
if there r other suggestions--as there is often more than 1 way to skin
a cat in XL, i'd be glad to take a look.
tx.

Try this:

=LOOKUP(99^99,SEARCH({"company","firm","corporatio n","partnership"},A1),{"company","firm","corporati on","partnership"})

Or, if you put the words in a range of cells:

A10:A13 = company; firm; corporation; partnership

=LOOKUP(99^99,SEARCH(A10:A13,A1),A10:A13)

If no match is found the formula will return #N/A.

Biff

"stef" <stef.bm_at_hotmail.removethis.com wrote in message
...
Ron,
tx for ur reply. it does work...however, i oversimplified the example in
my op.
i believe using FIND is better for me as s'times, the word "firm" for
example will be in the middle and not right, as in ABCfirmpartnership.
i do recognize the value or what u propose tho; it is helpful.
if there r other suggestions--as there is often more than 1 way to skin a
cat in XL, i'd be glad to take a look.
tx.


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!

biff, tx. it's clever and it works but i do need LEFT and RIGHT for my
current purposes; so the lookup is not enough unfortunately (see the
thread.)


Biff wrote:
Try this:

=LOOKUP(99^99,SEARCH({"company","firm","corporatio n","partnership"},A1),{"company","firm","corporati on","partnership"})

Or, if you put the words in a range of cells:

A10:A13 = company; firm; corporation; partnership

=LOOKUP(99^99,SEARCH(A10:A13,A1),A10:A13)

If no match is found the formula will return #N/A.

Biff

"stef" <stef.bm_at_hotmail.removethis.com wrote in message
...
Ron,
tx for ur reply. it does work...however, i oversimplified the example in
my op.
i believe using FIND is better for me as s'times, the word "firm" for
example will be in the middle and not right, as in ABCfirmpartnership.
i do recognize the value or what u propose tho; it is helpful.
if there r other suggestions--as there is often more than 1 way to skin a
cat in XL, i'd be glad to take a look.
tx.


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!

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!

OK....Try this:

A1: (source phrase)

The left part of the phrase
B1:
=LEFT(A1,LOOKUP(10^99,SEARCH({"|","Company","Firm" ,"Corporation","Partnership"},A1&"|"))-1)

The right part of the phrase
C1: =MID(A1,LEN(B1)+1,255)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"stef" wrote:

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!

i really do believe that i need to just add some kind of nested OR in my
existing formulas:

=RIGHT(A1,LEN(A1)-FIND({"firm","partners"},A1,1)+1) the {} doesn't work
here but something that would allow me to just insert "firm" OR
"partners" OR "company" OR "partnership", etc......

=LEFT(A1,FIND("partners",A1)-1) here as well


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!

1) Did you know that the FIND function is case-sensitive
and won't find "corp" if it's spelled "Corp" (with a capital C) ?

The SEARCH function is not case-sensitive.

2) My last post didn't help any?

***********
Regards,
Ron

XL2002, WinXP


"stef" wrote:

i really do believe that i need to just add some kind of nested OR in my
existing formulas:

=RIGHT(A1,LEN(A1)-FIND({"firm","partners"},A1,1)+1) the {} doesn't work
here but something that would allow me to just insert "firm" OR
"partners" OR "company" OR "partnership", etc......

=LEFT(A1,FIND("partners",A1)-1) here as well


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!

Ron,
it DID help. (and tx for the difference between FIND and SEARCH as far
as case sensitive: I will drop find and use search.)

the problem with the last post was the right formula that is dependent
on the left formula.

since some of the cells contains text OTHER THAN all of the
possibilities mentioned--which are under the bell of the norm distrib
curve, if u will--it doesn't produce the result for the right part of
the phrase in the instances which are outliers i.e. OTHER THAN the
possibilities accounted for.

between u and biff, i can probably combine the formulas and come up
w/s'thing close to what i want.

anyway, *how in the world* do i insert an OR (not the "OR" function)
into my original formula:

=RIGHT(A1,LEN(A1)-SEARCH({"firm","partners"},A1,1)+1) the {} doesn't
work here but something that would allow me to just insert "firm" OR
"partners" OR "company" OR "partnership", etc......

use "|" perhaps but where in the above formula? i would really like to
know how to do it......





Ron Coderre wrote:
1) Did you know that the FIND function is case-sensitive
and won't find "corp" if it's spelled "Corp" (with a capital C) ?

The SEARCH function is not case-sensitive.

2) My last post didn't help any?

***********
Regards,
Ron

XL2002, WinXP


"stef" wrote:

i really do believe that i need to just add some kind of nested OR in my
existing formulas:

=RIGHT(A1,LEN(A1)-FIND({"firm","partners"},A1,1)+1) the {} doesn't work
here but something that would allow me to just insert "firm" OR
"partners" OR "company" OR "partnership", etc......

=LEFT(A1,FIND("partners",A1)-1) here as well


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!

I'm a bit puzzled.....

If the Col_A value doesn't include any of the terms you're looking for,
I assumed you'd just want the whole phrase in Col_B and nothing in Col_C.
If there IS a match, Col_B includes up to the matched item and Col_C returns
from the matched item to the end of the phrase (matched item included).

Examples:
Col_A Col_B Col_C
my company my company
And my firm, too And my firm, too
This is a sentence This is a sentence (blank)

Are you looking for something else?

***********
Regards,
Ron

XL2002, WinXP


"stef" wrote:

Ron,
it DID help. (and tx for the difference between FIND and SEARCH as far
as case sensitive: I will drop find and use search.)

the problem with the last post was the right formula that is dependent
on the left formula.

since some of the cells contains text OTHER THAN all of the
possibilities mentioned--which are under the bell of the norm distrib
curve, if u will--it doesn't produce the result for the right part of
the phrase in the instances which are outliers i.e. OTHER THAN the
possibilities accounted for.

between u and biff, i can probably combine the formulas and come up
w/s'thing close to what i want.

anyway, *how in the world* do i insert an OR (not the "OR" function)
into my original formula:

=RIGHT(A1,LEN(A1)-SEARCH({"firm","partners"},A1,1)+1) the {} doesn't
work here but something that would allow me to just insert "firm" OR
"partners" OR "company" OR "partnership", etc......

use "|" perhaps but where in the above formula? i would really like to
know how to do it......





Ron Coderre wrote:
1) Did you know that the FIND function is case-sensitive
and won't find "corp" if it's spelled "Corp" (with a capital C) ?

The SEARCH function is not case-sensitive.

2) My last post didn't help any?

***********
Regards,
Ron

XL2002, WinXP


"stef" wrote:

i really do believe that i need to just add some kind of nested OR in my
existing formulas:

=RIGHT(A1,LEN(A1)-FIND({"firm","partners"},A1,1)+1) the {} doesn't work
here but something that would allow me to just insert "firm" OR
"partners" OR "company" OR "partnership", etc......

=LEFT(A1,FIND("partners",A1)-1) here as well


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!

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!