Find Specific Text in Multiple Cells

Hey there,

I'm trying to find a specific substring of text within a range of cells.

So right now my excel function is

Formula:

Formula:

Please Login or Register  to view this content.

In the current format I get all 0's.

What I would like the function to do is look in the range of cells for the text "Ambient Dub" ... and if this text is in one of the cells it gives it a 1, and if it is in none of the cells in that range it gives it a 0. The text does not have to be in every single cell... but somewhere within that range. I do not need to know what cell it came from.

Note: The text is only going to be in one of the cells, but sometimes it is written in the second column rather than the first.

Hi jrbrugger,

Try this formula:

=IF(ISERROR(HLOOKUP("Ambient Dub",C2:V2,1,FALSE)),0,1)

HTH

Robert

You could try this:

=IF(COUNTIF(C2:V2,"*"&"Ambient Dub"&"*"),1,0)

- Moo

hi jrbrugger. the problem with your formula is that the search formula is going to give you multiple results. for eg. if "Ambient Dub" is in C1, then your results for the SEARCH formula would be:
{#VALUE!,1,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!,#VALUE!}
notice that only the 2nd portion is shown 1. using ISNUMBER, it will then be:
{FALSE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

this is an array results. and excel would not know which result to show you. while it's possible to still get a result, there is an easier way. =COUNTIF(C2:V2,"*Ambient Dub*")

if you have a possibility of many "Ambient Dub" results & you only want to see "1" shown, then:
=IF(COUNTIF(C2:V2,"*Ambient Dub*"),1,0)

added a little explanation & slower than Moo. Long time no see, Moo!

Hi benishiryo... long time no see, indeed. But I've been lurking. Just don't have a lot of time to spend on here with kids and life in general ... I try to pop in and help out when I can. =)

Hope you are well.

- Moo