Hi Stef

I couldn't get the array of values with Countif to work, but with B1
containing

=RIGHT(A1,SUM(COUNTIF(A1,"*firm")*4+
COUNTIF(A1,"*llc")*3)+COUNTIF(A1,"*company")*7
+COUNTIF(A1,"*partnership")*11)

and C1 containing
=SUBSTITUTE(A1,B1,"")
It seemed to give the answers you were looking for.

--
Regards

Roger Govier


"stef" <stef.bm_at_hotmail.removethis.com wrote in message
...
Ron, i am a bit puzzled myself :)

no i'm not looking 4 something else; i think where there is some kind
of small breakdown is that i cannot predict exactly the words such as
"company", "firm", etc. to include all of the possibilities including
not being there at all e.g. "ABC" as opposed to "ABCcompany" or
"ABCfirm", etc.
So, if column B starts returning an error value, because none of the
words appear; then of course column C will not return ANY value but an
error.
on the other hand, using the original LEFT and RIGHT only and
independently of each other seems to return proper values EXCEPT for
the "OR" statement i still haven't figured out how to insert or nest
w/in the formula.

so in short, between
=RIGHT(A1,SUM(COUNTIF(A1,{"*company","*firm,"partn ership","*llc",et
al})*{7,4,11,3}))

and;

=LEFT(A1,LOOKUP(10^99,SEARCH(w_list,A1&"|"))-1) (where w_list is a
list of names i am searching) and

i can make it work.

i am still wondering however if u can explain how to insert the
concept of "OR" in my original formula so that i can learn 4 the
future.



Ron Coderre wrote:
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!