Value within a cell
 

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

Value within a cell
 

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

"N1KO" wrote:

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

Value within a cell
 

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

--
Gary''s Student - gsnu200903


"N1KO" wrote:

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

Value within a cell
 

Use this normally entered formula...

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

--
Rick (MVP - Excel)


"N1KO" wrote in message
...
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

Value within a cell
 

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

Niko

Value within a cell
 

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

Thanks though.

Niko

"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200903


"N1KO" wrote:

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