Return a result for the appearance of a text string in any cell in a row (EXCEL 2010)

Type it in AW1:
=SUMPRODUCT(--ISNUMBER(SEARCH("ascend",A1:AV1)))>0
give TRUE / FALSE if found / not found the word "ascend"
You can use FIND if the word is case-sensitive.
Filter the column AW with TRUE.

Hi,

When you say that bebo's solution "didn't work", can you be a bit more specific? What result did you get? What result were you expecting, and why?

Also, did you amend the range references to match your actual set-up, if necessary?

Regards

Nice to see it works.
SUMPRODUCT works with numbers, while ISNUMBER gives TRUE/FALSE. --will converts TRUE/FALSE into 1/0 (Also, numbers store as text into numbers)

Ok. Glad you got it sorted.

The double-unary, as its known, is actually two 'minus' symbols operating, consecutively, on the array of returns resulting from the SUMPRODUCT. For example, you might have the following return of Boolean responses for each of the cells in A1:AV1 as to whether they contain the string "ascend" (I won't list them all):

{FALSE,FALSE,FALSE,FALSE,FALSE,TRUE,FALSE,TRUE,TRUE,TRUE,FALSE,TRUE,TRUE,FALSE,FALSE,FALSE,TRUE,...}

Since, effectively, we wish to count the TRUEs as 1 and the FALSEs as 0 (this is precisely how functions like SUMPRODUCT work), we must first coerce them into those (numerical) values. Fortunately, any suitable mathematical operation (i.e one which leaves the value unchanged) applied to a Boolean return of TRUE/FALSE will do just that: TRUE*1 = 1*1 = 1, 1*FALSE = 1*0 = 0, TRUE+0 = 1+0 = 1, etc.

Multiplying by -1 (putting a single minus sign in front of the array) is another mathematical operation which would coerce the TRUE/FALSE strings into numerical values, though the results would be a string of -1s (for the TRUEs), which would not give the desired results. Hence the second minus sign, which "negates the negation" (and so returns +1s).

If it's any easier to comprehend, bebo's formula could equally have been written:

=SUMPRODUCT(0+ISNUMBER(SEARCH("ascend",A1:AV1)))>0

or

=SUMPRODUCT(1*ISNUMBER(SEARCH("ascend",A1:AV1)))>0

Regards