PROBLEM: finding text within text and provide feedback


Posted by Conny Gallenz on January 18, 2002 8:28 AM

Hi,
I have one column (A) that contains long descriptions (multiple words) of text and I would like to run a query that does the following for each line (1 to 500):

if a cell in column A contains the word "pen" or "Waterman" or "BIC" then show the value PENS in column B, or
if a cell in column A contains the word "label" or "Avery" then show the value LABEL in column B, or
if a cell in column A contains the word "sheet" or "paper" or "11x17" then show the value PAPER SUPPLIES in column B, or
if no matching value is found, then show the value "UNKNOWN" in column B.

To remember is that these words like "pen" can be part of a long text entry ("blue round pen with red imprint") or in the very beginning of the cell ("pen with blue imprint").

I have not figured out how to write this check and action depending on the findings.

Any help would be greatly appreciated!



Posted by Aladin Akyurek on January 18, 2002 9:28 AM

Conny --

Try:

=IF(SUMPRODUCT(ISNUMBER(SEARCH({"pen","Waterman","BIC"},A1))+0),"PENS",IF(SUMPRODUCT(ISNUMBER(SEARCH({"label","Avery"},A1))+0),"LABEL",IF(SUMPRODUCT(ISNUMBER(SEARCH({"sheet","paper","11X17"},A1))+0),"PAPER SUPPLIES","UNKNOWN")))

Caveat: Simultaneous occurrences of the search objects such as "pen" and "label" will be processed according to the sequence you mention. In case of "pen" plus "label", you'll get "PENS" as result.

Aladin

===========