Excel: cell contains string from range

June 18, 2009 at 13:51:05
Specs: Windows Vista
In MS Excel 2007

the question is, using only a Formula (no VBA), how to determin is the string in a cell contains any key words listed out on another sheet.

Example:
Assume cell A2 contains the text:
"it is necessary that you post a detailed description of your problem"

On another tab, assume there is a list of other strings what we want to search for. One string per row.

please
post
elephant

The formula should return true since A1 contains the word "post" from the keywords


See More: Excel: cell contains string from range

Report •


#1
June 18, 2009 at 15:12:34
First off, are you sure your cell references are correct?

You said the text (sentence) is in A2 and then you said "The formula should return true since A1 contains the word "post" from the keywords".

Do you mean "since A2 contains the word "post" from the keywords"?

Assuming the sentence is in A2 and your list is in Sheet2!A1:A3, try this array formula:

=IF(SUM(NOT(ISERROR(FIND(Sheet2!$A$1:$A$3,A2)))*1)>0,TRUE,FALSE)

This formula must be entered using Ctrl-Shift-Enter whenever you edit it. It should appear with {} around it after you use Ctrl-Shift-Enter.


Report •
Related Solutions


Ask Question