Value within a cell

[N1KO]

Hi,

Is there any code to find a phrase (that i enter into cell E1) within a cell.

For example if

B2 holds "The Cat in the hat"
B3 holds "The Cat in the Car"
B4 holds "The Dog in the Bush"
B5 holds "Harry and the monkey"

I want to search for the number of cells that hold the phrase "in the". In
this case that'd be 3

Is it possible to do this?

Thanks in advance

Niko

 

[ker_01]

In cell E1:

=COUNT(FIND("in the",B2:B5))

Enter as an array formula (control-shift-enter)

Note that this is case sensitive, and will not match "In the" or "in The"

HTH,
Keith

 

[Gary''s Student]

=(LEN(B2&B3&B4&B5)-LEN(SUBSTITUTE(B2&B3&B4&B5,"in the","")))/6

 

[Rick Rothstein]

Use this normally entered formula...

=COUNTIF(B2:B5,"*"&E1&"*")

 

[N1KO]

Thanks to you all I'll have a go through these in the morning.

Niko

 

[N1KO]

This worked but is going to be way too long for the range i need.

Thanks though.

Niko