Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check text in cell for at least one keyword from a list on anothersheet
I have a list of company names, some of which were dummy companies set
up for testing. those names are often things like 'dummy company' or 'test no.2 company' etc. I'm currently doing a long manual workaround which involves doing multiple iterations of custom autofilter e.g. contains 'test' delete rows, contains 'demo' delete rows. I'm trying to create a formula that output of which will be a single value which I can autofilter against and then delete the offending rows all in one hit. i.e. check the text in this cell for the presence of any of the keywords in this list and if you find one return the value 'found one'. I've tried using an array formula e.g. {=FIND(Keywords!B2:B7,A2)} and my list looking like "test" "demo" "dummy" but just get the error #VALUE? help gratefully received. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check text in cell for at least one keyword from a list on another sheet
This formula returns TRUE for items containing a keyword:
=COUNT(INDEX(SEARCH(Keywords!$B$2:$B$7,A2),0))0 Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Brotherharry" wrote in message ... I have a list of company names, some of which were dummy companies set up for testing. those names are often things like 'dummy company' or 'test no.2 company' etc. I'm currently doing a long manual workaround which involves doing multiple iterations of custom autofilter e.g. contains 'test' delete rows, contains 'demo' delete rows. I'm trying to create a formula that output of which will be a single value which I can autofilter against and then delete the offending rows all in one hit. i.e. check the text in this cell for the presence of any of the keywords in this list and if you find one return the value 'found one'. I've tried using an array formula e.g. {=FIND(Keywords!B2:B7,A2)} and my list looking like "test" "demo" "dummy" but just get the error #VALUE? help gratefully received. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check text in cell for at least one keyword from a list on another
I've used this with a great deal of success:
=IF(NOT(ISERROR(MATCH(A1:A6,B1:B6,0))),A1:A6,"") It is an array function, so you must enter it using Ctrl + Shift + Enter, not just Enter. Changes the ranges to suit your needs... Regards, Ryan-- -- RyGuy "Brotherharry" wrote: I have a list of company names, some of which were dummy companies set up for testing. those names are often things like 'dummy company' or 'test no.2 company' etc. I'm currently doing a long manual workaround which involves doing multiple iterations of custom autofilter e.g. contains 'test' delete rows, contains 'demo' delete rows. I'm trying to create a formula that output of which will be a single value which I can autofilter against and then delete the offending rows all in one hit. i.e. check the text in this cell for the presence of any of the keywords in this list and if you find one return the value 'found one'. I've tried using an array formula e.g. {=FIND(Keywords!B2:B7,A2)} and my list looking like "test" "demo" "dummy" but just get the error #VALUE? help gratefully received. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check text in cell for at least one keyword from a list onanother sheet
Nope, just get a column of FALSEs... On 10 Dec, 14:57, "Ron Coderre" wrote: This formula returns TRUE for items containing a keyword: =COUNT(INDEX(SEARCH(Keywords!$B$2:$B$7,A2),0))0 Does that help? Post back if you have more questions. -------------------------- Regards, Ron Microsoft MVP (Excel) (XL2003, Win XP) "Brotherharry" wrote in message ... I have a list of company names, some of which were dummy companies set up for testing. those names are often things like 'dummy company' or 'test no.2 company' etc. I'm currently doing a long manual workaround which involves doing multiple iterations of custom autofilter e.g. contains 'test' delete rows, contains 'demo' delete rows. I'm trying to create a formula that output of which will be a single value which I can autofilter against and then delete the offending rows all in one hit. i.e. check the text in this cell for the presence of any of the keywords in this list and if you find one return the value 'found one'. I've tried using an array formula e.g. {=FIND(Keywords!B2:B7,A2)} and my list looking like "test" "demo" "dummy" but just get the error #VALUE? help gratefully received. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
check text in cell for at least one keyword from a list onanother
also get just a column of blanks....?
chances of two independent approaches both producing the same lack of effect are remote, so wonder if it's my settings somewhere? using Excel 2007 On 10 Dec, 15:03, ryguy7272 wrote: I've used this with a great deal of success: =IF(NOT(ISERROR(MATCH(A1:A6,B1:B6,0))),A1:A6,"") It is an array function, so you must enter it using Ctrl + Shift + Enter, not just Enter. Changes the ranges to suit your needs... Regards, Ryan-- -- RyGuy "Brotherharry" wrote: I have a list of company names, some of which were dummy companies set up for testing. those names are often things like 'dummy company' or 'test no.2 company' etc. I'm currently doing a long manual workaround which involves doing multiple iterations of custom autofilter e.g. contains 'test' delete rows, contains 'demo' delete rows. I'm trying to create a formula that output of which will be a single value which I can autofilter against and then delete the offending rows all in one hit. i.e. check the text in this cell for the presence of any of the keywords in this list and if you find one return the value 'found one'. I've tried using an array formula e.g. {=FIND(Keywords!B2:B7,A2)} and my list looking like "test" "demo" "dummy" but just get the error #VALUE? help gratefully received. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I see if a cell contains a keyword from a list? | Excel Worksheet Functions | |||
using a check box to display text from a list | Charts and Charting in Excel | |||
Insert text in Cell A1 based on keyword criteria | Charts and Charting in Excel | |||
Check if cell entry belongs to a name list-how? | Excel Discussion (Misc queries) | |||
Finding a Keyword in a Cell | Excel Discussion (Misc queries) |