text exist
I have misplaced a formula that I had in the past to accomplish the
following: A cell contains a string of text and within that text are certain keywords that I need to determine if exists within a table/array of critical keywords. For example if I have a text field that contains the phrase "property taxes are due Jan 1" and in have a keyword table/array that contains the word "taxes" as one of the entries I would like to flag that particular entry. I believe I had accomplished this with the use of the search formula in the past but I have been unable to reproduce. Any assistance would be appreciated as I have exhaused my knowledge... |
text exist
Range of keywords in range A2:A10
Cell to search is B2 =SUMPRODUCT(ISNUMBER(SEARCH($A$2:$A$10,B2))*1) will tell you how many key words are found within cell. -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "gpurdue" wrote: I have misplaced a formula that I had in the past to accomplish the following: A cell contains a string of text and within that text are certain keywords that I need to determine if exists within a table/array of critical keywords. For example if I have a text field that contains the phrase "property taxes are due Jan 1" and in have a keyword table/array that contains the word "taxes" as one of the entries I would like to flag that particular entry. I believe I had accomplished this with the use of the search formula in the past but I have been unable to reproduce. Any assistance would be appreciated as I have exhaused my knowledge... |
text exist
In F1:F4 I have this as a list of 'keywords"
taxes bribes death justice In A1:A3 I have these test phases property taxes are due Jan 1 hello death and taxes In B1 I have this formula =SUMPRODUCT(--ISNUMBER(SEARCH($F$1:$F$4,A1))) It is copied down to B3 The values in B1:B3 are; 1, 0, 2 Does this do what you want? You could use the formula in Conditional Formatting to colour the appropriate cells You could use =SUMPRODUCT(--ISNUMBER(SEARCH($F$1:$F$4,A1)))0 to get TRUE or FALSE best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "gpurdue" wrote in message ... I have misplaced a formula that I had in the past to accomplish the following: A cell contains a string of text and within that text are certain keywords that I need to determine if exists within a table/array of critical keywords. For example if I have a text field that contains the phrase "property taxes are due Jan 1" and in have a keyword table/array that contains the word "taxes" as one of the entries I would like to flag that particular entry. I believe I had accomplished this with the use of the search formula in the past but I have been unable to reproduce. Any assistance would be appreciated as I have exhaused my knowledge... |
text exist
Assuming that A2 contains the text string, and G2:G6 contains the
keyword table, try... =IF(A2<"",LOOKUP(9.99999999999999E+307,SEARCH(" "&$G$2:$G$6&" "," "&A2&" "),$G$2:$G$6),"") Or, if you prefer, try... =IF(A2<"",IF(ISNUMBER(LOOKUP(9.99999999999999E+30 7,SEARCH(" "&$G$2:$G$6&" "," "&A2&" "))),1,""),"") -- Domenic Microsoft Excel MVP www.xl-central.com Your Quick Reference to Excel Solutions In article , gpurdue wrote: I have misplaced a formula that I had in the past to accomplish the following: A cell contains a string of text and within that text are certain keywords that I need to determine if exists within a table/array of critical keywords. For example if I have a text field that contains the phrase "property taxes are due Jan 1" and in have a keyword table/array that contains the word "taxes" as one of the entries I would like to flag that particular entry. I believe I had accomplished this with the use of the search formula in the past but I have been unable to reproduce. Any assistance would be appreciated as I have exhaused my knowledge... |
All times are GMT +1. The time now is 09:51 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com