Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions
|
|||
|
|||
using nested OR
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! |
#2
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#3
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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. |
#4
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#5
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#6
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#7
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#8
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#9
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#10
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#11
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#12
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#13
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
the problem with the =LOOKUP(99^99,SEARCH(w_list,G131),w_list) is that
is returns #N/A if none of the words in the list are found. using RIGHT or LEFT wd get around that problem i believe since in my original formula it counts the characters, etc.... still learning but i guess i'm trying to get it exactly right..... 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! |
#14
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
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! |
#15
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
Just curious, Roger....
Which COUNTIF array formula wouldn't work for you? BTW...regarding: and C1 containing =SUBSTITUTE(A1,B1,"") I *KEEP* forgetting that one. *********** Regards, Ron XL2002, WinXP "Roger Govier" wrote: 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! |
#16
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
Roger,
tx a lot. let me look into it and i'll post here again. And to everyone posting in this thread, i do very much appreciate ur help and patience. Roger Govier wrote: 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. |
#17
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
I took the liberty to modify your formula
so a separate list can be used: Groups firm llc company partnership =RIGHT(A1,SUMPRODUCT(COUNTIF(A1,"*"&Groups)*LEN(Gr oups))) |
#18
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
using nested OR
Hi Ron
I was wrong. I copied Stef's formula of =RIGHT(A1,SUM(COUNTIF(A1,{"*company","*firm,"partn ership","*llc",et al})*{7,4,11,3})) I adjusted to get rid of the "et al" bit, but failed to notice the missing " after firm.(and missing * before partnership) It was very late, and I just thought (wrongly) Countif wouldn't take an array of values, so went the long way round!!! Your much earlier posting of =RIGHT(A1,SUM(COUNTIF(A1,{"*Company","*Firm","*Cor poration","*Partnership"})*{7,4,11,11})) which I have now read, works perfectly. I do like Herbert's later posting with Sumproduct. It provides a very flexible solution. -- Regards Roger Govier "Ron Coderre" wrote in message ... Just curious, Roger.... Which COUNTIF array formula wouldn't work for you? BTW...regarding: and C1 containing =SUBSTITUTE(A1,B1,"") I *KEEP* forgetting that one. *********** Regards, Ron XL2002, WinXP "Roger Govier" wrote: 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! |
#19
Posted to microsoft.public.excel,microsoft.public.excel.worksheet.functions
|
|||
|
|||
using nested OR
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Nested Sub-Totals in Excel | Excel Discussion (Misc queries) | |||
Nested IF statement with VLOOKUP | Excel Discussion (Misc queries) | |||
Nested Subtotals in Excel 2002 | Excel Discussion (Misc queries) | |||
Why are my nested sub-totals are displaying incorrectly? | Excel Discussion (Misc queries) | |||
Nested IF statements | Excel Worksheet Functions |