Find Text

[teeb]

I'm currently using the following formula for looking for certain text in a certain cell:

=IF(ISNUMBER(SEARCH("death",A1)),"yes","")

so if the cell has "death" it returns a "yes".

What I'm looking for is a this to look at a range of columns not just one cell.
ie A1:F1 and if any of the cells in this range I need the result to be "yes"

Any help would be appreciated.

Teeb

[sweep]

Hi,

You can concatenate the cells you want to check like this:

=IF(ISNUMBER(SEARCH("death",A1&B1&C1&D1&E1&F1)),"yes","")

[roly]

Hi,

You can't use the search function for more than one cell.

Assuming you are looking for different values in the different cells, you can use the OR function which tests up to 30 criteria (separated by a comma) Use them in an IF statement =IF(OR(ISNUMBER(SEARCH("death",A1)), and your next criteria ,"yes","")))

In a similar manner you can use the AND when you want more than 1 criterion to be in the cells.

HTH,


Roly

[tek]

Maybe an array function. Not sure but this page seems to have a solution

http://www.emailoffice.com/excel/arrays-bobumlas.html

tek

[teeb]

Thanks guys!! The concenate worked.

Cheers, Teeb