|
Text fomula
In cell B1 I could have any one of the following text examples: big apple,
small apple, orange, apple, pear etc. In A1 I want a formula that will return "apple" if B1 contains any of the text apple. ie if it read "big apple" in B1 I want it to return "apple" in A1. Thanks GK |
Check out the FIND() and SEARCH() functions. Then you could write an
IF() in A1 that says 'if the result of the FIND is greater than zero, write "apple" in A1'. |
One way:
=IF(COUNTIF(B1,"*apple*"),"apple","") In article , "gregork" wrote: In cell B1 I could have any one of the following text examples: big apple, small apple, orange, apple, pear etc. In A1 I want a formula that will return "apple" if B1 contains any of the text apple. ie if it read "big apple" in B1 I want it to return "apple" in A1. Thanks GK |
=if(counif(B1,"*apple*)0,"apple","")
-- Regards, Tom Ogilvy "gregork" wrote in message ... In cell B1 I could have any one of the following text examples: big apple, small apple, orange, apple, pear etc. In A1 I want a formula that will return "apple" if B1 contains any of the text apple. ie if it read "big apple" in B1 I want it to return "apple" in A1. Thanks GK |
Left of a double quote:
=if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
Thanks Guys.....does exactly what I wanted.
Cheers GK "Tom Ogilvy" wrote in message ... Left of a double quote: =if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
Tom,
Suppose in A1:F6 there can be all kinds of things (text, numbers, blanks, formulae), but I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once. If so a formula in H1 should return TRUE, if not FALSE. I tried =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},A1:F6,0))=5 but it won't work, also when array entered. Your help will be appreciated. Jack Sons The Netherlands "Tom Ogilvy" schreef in bericht ... Left of a double quote: =if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
try type in H1
=IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y"))=1,TRUE,FALSE) your data is in d5 to d16. do you get what you want. may be tortuous to write .may be bugs in it. try the experiment ==================================== Jack Sons wrote in message ... Tom, Suppose in A1:F6 there can be all kinds of things (text, numbers, blanks, formulae), but I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once. If so a formula in H1 should return TRUE, if not FALSE. I tried =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},A1:F6,0))=5 but it won't work, also when array entered. Your help will be appreciated. Jack Sons The Netherlands "Tom Ogilvy" schreef in bericht ... Left of a double quote: =if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
sorry. this is not working on subsequent experiments
apologise. R.VENKATARAMAN $$$ wrote in message news:... try type in H1 =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y"))=1,TRUE,FALSE) your data is in d5 to d16. do you get what you want. may be tortuous to write .may be bugs in it. try the experiment ==================================== Jack Sons wrote in message ... Tom, Suppose in A1:F6 there can be all kinds of things (text, numbers, blanks, formulae), but I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once. If so a formula in H1 should return TRUE, if not FALSE. I tried =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},A1:F6,0))=5 but it won't work, also when array entered. Your help will be appreciated. Jack Sons The Netherlands "Tom Ogilvy" schreef in bericht ... Left of a double quote: =if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
strangely it works with
=IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y")),TRUE,FALSE) i.e without =1 But I could not explain the logic ============================= R.VENKATARAMAN $$$ wrote in message ... sorry. this is not working on subsequent experiments apologise. R.VENKATARAMAN $$$ wrote in message news:... try type in H1 =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y"))=1,TRUE,FALSE) your data is in d5 to d16. do you get what you want. may be tortuous to write .may be bugs in it. try the experiment ==================================== Jack Sons wrote in message ... Tom, Suppose in A1:F6 there can be all kinds of things (text, numbers, blanks, formulae), but I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once. If so a formula in H1 should return TRUE, if not FALSE. I tried =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},A1:F6,0))=5 but it won't work, also when array entered. Your help will be appreciated. Jack Sons The Netherlands "Tom Ogilvy" schreef in bericht ... Left of a double quote: =if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
R,
Thank you very much, it indeed works, also for D5:E16 (what I was looking for, more that one dimension). What also works is =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I got stuck with this problem, I really want it solved, also because the list or array way (how should I call it?), with {......} is so elegant and easier to use. Can you or anybody out there in this august newsgroup help (and explain)? Jack. "R.VENKATARAMAN" $$$ schreef in bericht ... strangely it works with =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y")),TRUE,FALSE) i.e without =1 But I could not explain the logic ============================= R.VENKATARAMAN $$$ wrote in message ... sorry. this is not working on subsequent experiments apologise. R.VENKATARAMAN $$$ wrote in message news:... try type in H1 =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y"))=1,TRUE,FALSE) your data is in d5 to d16. do you get what you want. may be tortuous to write .may be bugs in it. try the experiment ==================================== Jack Sons wrote in message ... Tom, Suppose in A1:F6 there can be all kinds of things (text, numbers, blanks, formulae), but I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once. If so a formula in H1 should return TRUE, if not FALSE. I tried =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},A1:F6,0))=5 but it won't work, also when array entered. Your help will be appreciated. Jack Sons The Netherlands "Tom Ogilvy" schreef in bericht ... Left of a double quote: =if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
Why even use the IF()?
=OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pears" ),COUNTIF(D5:D16,"cherry") ) OR, even simpler: =OR(COUNTIF(D5:D16,{"apple","pears","cherry"})) -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "R.VENKATARAMAN" $$$ wrote in message ... strangely it works with =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y")),TRUE,FALSE) i.e without =1 But I could not explain the logic ============================= R.VENKATARAMAN $$$ wrote in message ... sorry. this is not working on subsequent experiments apologise. R.VENKATARAMAN $$$ wrote in message news:... try type in H1 =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y"))=1,TRUE,FALSE) your data is in d5 to d16. do you get what you want. may be tortuous to write .may be bugs in it. try the experiment ==================================== Jack Sons wrote in message ... Tom, Suppose in A1:F6 there can be all kinds of things (text, numbers, blanks, formulae), but I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once. If so a formula in H1 should return TRUE, if not FALSE. I tried =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},A1:F6,0))=5 but it won't work, also when array entered. Your help will be appreciated. Jack Sons The Netherlands "Tom Ogilvy" schreef in bericht ... Left of a double quote: =if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
I replied to R without reading the entire thread.
For your question, replace the "OR" and try this: =AND(COUNTIF(D5:D16,{"apple","pears","cherry"})) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Sons" wrote in message ... R, Thank you very much, it indeed works, also for D5:E16 (what I was looking for, more that one dimension). What also works is =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:D16,0))=5 but =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},D5:E16,0))=5 does not work. I got stuck with this problem, I really want it solved, also because the list or array way (how should I call it?), with {......} is so elegant and easier to use. Can you or anybody out there in this august newsgroup help (and explain)? Jack. "R.VENKATARAMAN" $$$ schreef in bericht ... strangely it works with =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y")),TRUE,FALSE) i.e without =1 But I could not explain the logic ============================= R.VENKATARAMAN $$$ wrote in message ... sorry. this is not working on subsequent experiments apologise. R.VENKATARAMAN $$$ wrote in message news:... try type in H1 =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y"))=1,TRUE,FALSE) your data is in d5 to d16. do you get what you want. may be tortuous to write .may be bugs in it. try the experiment ==================================== Jack Sons wrote in message ... Tom, Suppose in A1:F6 there can be all kinds of things (text, numbers, blanks, formulae), but I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once. If so a formula in H1 should return TRUE, if not FALSE. I tried =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},A1:F6,0))=5 but it won't work, also when array entered. Your help will be appreciated. Jack Sons The Netherlands "Tom Ogilvy" schreef in bericht ... Left of a double quote: =if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
yes elegant. thanks
RagDyeR wrote in message ... Why even use the IF()? =OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pears" ),COUNTIF(D5:D16,"cherry") ) OR, even simpler: =OR(COUNTIF(D5:D16,{"apple","pears","cherry"})) -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "R.VENKATARAMAN" $$$ wrote in message ... strangely it works with =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y")),TRUE,FALSE) i.e without =1 But I could not explain the logic ============================= R.VENKATARAMAN $$$ wrote in message ... sorry. this is not working on subsequent experiments apologise. R.VENKATARAMAN $$$ wrote in message news:... try type in H1 =IF(OR(COUNTIF(D5:D16,"apple"),COUNTIF(D5:D16,"pea rs"),COUNTIF(D5:D16,"cherr y"))=1,TRUE,FALSE) your data is in d5 to d16. do you get what you want. may be tortuous to write .may be bugs in it. try the experiment ==================================== Jack Sons wrote in message ... Tom, Suppose in A1:F6 there can be all kinds of things (text, numbers, blanks, formulae), but I want to check if "apple", "pear", "cherry"; "coconut" and "apricot" each occur at least once. If so a formula in H1 should return TRUE, if not FALSE. I tried =COUNT(MATCH({"apple", "pear", "cherry"; "coconut","apricot"},A1:F6,0))=5 but it won't work, also when array entered. Your help will be appreciated. Jack Sons The Netherlands "Tom Ogilvy" schreef in bericht ... Left of a double quote: =if(counif(B1,"*apple*")0,"apple","") -- Regards, Tom Ogilvy |
| All times are GMT +1. The time now is 03:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com