| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need formula to locate and validate specific text from a string
Hi,
I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a string
Hello,
Try; =IF(OR(ISNUMBER(SEARCH("testing",A1)),ISNUMBER(SEA RCH("cold",A1)),ISNUMBER(SEARCH("working",A1))),"Y es","No") "vinstream" wrote in message ... Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a string
Thanks a ton...this is working.
How many If statements can I use in one formula? On Apr 7, 11:12*am, "ozgrid.com" wrote: Hello, Try; =IF(OR(ISNUMBER(SEARCH("testing",A1)),ISNUMBER(SEA RCH("cold",A1)),ISNUMBER(*SEARCH("working",A1)))," Yes","No")"vinstream" wrote in message ... Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream- Hide quoted text - - Show quoted text - |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a string
For cell B2:
=IF(OR(IF(ISERR(FIND("testing",A2)),FALSE,TRUE),IF (ISERR(FIND("cold",A2)),FALSE,TRUE),IF(ISERR(FIND( "working",A2)),FALSE,TRUE)),"Yes","No") Fill that down to the end of your list. Remember that the formula should be entered as one continuous line in Excel; the system here tends to break long formulas into several lines. "vinstream" wrote: Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream . |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a string
Oops, turns out to work better if you use SEARCH instead:
=IF(OR(IF(ISERR(SEARCH("testing",A3)),FALSE,TRUE), IF(ISERR(SEARCH("cold",A3)),FALSE,TRUE),IF(ISERR(S EARCH("working",A3)),FALSE,TRUE)),"Yes","No") "vinstream" wrote: Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream . |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a string
Would I be right in saying that you could change IF(condition,FALSE,TRUE) to
NOT(condition) ? Hence =IF(OR(NOT(ISERR(SEARCH("testing",A3))),NOT(ISERR( SEARCH("cold",A3))),NOT(ISERR(SEARCH("working",A3) ))),"Yes","No") and then presumably =IF(AND(ISERR(SEARCH("testing",A3)),ISERR(SEARCH(" cold",A3)),ISERR(SEARCH("working",A3))),"No","Yes" ) ? -- David Biddulph "JLatham" wrote in message ... Oops, turns out to work better if you use SEARCH instead: =IF(OR(IF(ISERR(SEARCH("testing",A3)),FALSE,TRUE), IF(ISERR(SEARCH("cold",A3)),FALSE,TRUE),IF(ISERR(S EARCH("working",A3)),FALSE,TRUE)),"Yes","No") "vinstream" wrote: Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream . |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a strin
Some shorter formula:
=IF(ISERR(SEARCH("testing",A1))*ISERR(SEARCH("cold ",A1))*ISERR(SEARCH("working",A1))=0,"Yes","No ") Micky "JLatham" wrote: Oops, turns out to work better if you use SEARCH instead: =IF(OR(IF(ISERR(SEARCH("testing",A3)),FALSE,TRUE), IF(ISERR(SEARCH("cold",A3)),FALSE,TRUE),IF(ISERR(S EARCH("working",A3)),FALSE,TRUE)),"Yes","No") "vinstream" wrote: Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream . |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a string
Seven pre Excel 2007, but there are better ways.
http://www.ozgrid.com/Excel/seven-nested.htm http://www.ozgrid.com/Excel/nested-function-limit.htm -- Regards Dave Hawley www.ozgrid.com "vinstream" wrote in message ... Thanks a ton...this is working. How many If statements can I use in one formula? On Apr 7, 11:12 am, "ozgrid.com" wrote: Hello, Try; =IF(OR(ISNUMBER(SEARCH("testing",A1)),ISNUMBER(SEA RCH("cold",A1)),ISNUMBER(*SEARCH("working",A1)))," Yes","No")"vinstream" wrote in message ... Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream- Hide quoted text - - Show quoted text - |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a strin
As you can learn from my solution you don't need more than one IF statement.
Basically: Up to "Excel 2003" (incl.) you can nest 7 IF Statements. From "Excel 2007" you can nest 64 IF Statements. Usually, no one nests that many statements - not even 7 In such cases one uses a small table with all the possibilities and returns the result with VLOOKUP. Micky "vinstream" wrote: Thanks a ton...this is working. How many If statements can I use in one formula? On Apr 7, 11:12 am, "ozgrid.com" wrote: Hello, Try; =IF(OR(ISNUMBER(SEARCH("testing",A1)),ISNUMBER(SEA RCH("cold",A1)),ISNUMBER(*SEARCH("working",A1))), "Yes","No")"vinstream" wrote in message ... Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream- Hide quoted text - - Show quoted text - . |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a string
On Tue, 6 Apr 2010 22:51:40 -0700 (PDT), vinstream wrote:
Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream Another approach: =IF(OR(COUNTIF(A1,"*"&{"testing","cold","working"} &"*")),"yes","no") --ron |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a string
On Tue, 6 Apr 2010 22:51:40 -0700 (PDT), vinstream wrote:
Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream By the way, mine and the other approaches will also return "yes" if the word being tested is a Part of the phrase. For example, if you were testing for 'man', the various formulas would return "yes" for Germany Germany (man) If this could be a problem, you could modify the formulas to also look for the "(", assuming your format is always as above. If there are more variations, a different approach might work better. For mine: =IF(OR(COUNTIF(A1,"*("&{"testing","cold","working" }&")*")),"yes","no") --ron |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need formula to locate and validate specific text from a string
One mo
=IF(SUM(COUNTIF(A1,"*"&{"testing","working","cold" }&"*"))0,"Yes","No") You could actually leave the "0" out of the expression. But I think it makes the formula easier to understand. vinstream wrote: Hi, I have the following sample data in column A. germany (testing) Germany (cold) Germany Austria (Testing) Austria (cold) Austria (working) Austria (other) China China (alternate) In column B, I want it to show that if the cell to the left has any of the words 'testing' or 'cold' or 'working' it should show "yes" in Column B else show "no". Is this possible? Regards, Vinstream -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
| locate cell with specific text and select that column | Excel Discussion (Misc queries) | |||
| Formula to extract a specific word from text string | Excel Worksheet Functions | |||
| Locate and count the recurrences of a text string | Excel Discussion (Misc queries) | |||
| can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
Linear Mode
