String function
 

I have a long string of words in one cell. Is there a
function that I can use to search a range of cells and
count the cells that contain that specific phrase?

String function
 

In Excel there is a function called Find and in VBA there is a function
called Instr. Take a look at those. Your question is a little unclear in that
you say that the string is in one cell but you want to know which cells
contain the phrase.

HTH

"jason" wrote:

I have a long string of words in one cell. Is there a
function that I can use to search a range of cells and
count the cells that contain that specific phrase?

String function
 

Hi Jason,

jason wrote:
I have a long string of words in one cell. Is there a
function that I can use to search a range of cells and
count the cells that contain that specific phrase?

Are you looking for VBA code to do this, or just a worksheet function? If
the latter, you could try this array formula:

=SUM(IF(ISERR(SEARCH($A$1,$C$1:$C$4)),0,1))

This assumes the string to be looked for is in A1 and the range to be
searched is C1:C4. If you need a case-sensitive search, you can use FIND
instead of SEARCH.

This must be entered using Ctrl+Shift+Enter.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

String function
 

thanks jake. i'll give it a try.
-----Original Message-----
Hi Jason,

jason wrote:
I have a long string of words in one cell. Is there a
function that I can use to search a range of cells and
count the cells that contain that specific phrase?

Are you looking for VBA code to do this, or just a
worksheet function? If
the latter, you could try this array formula:

=SUM(IF(ISERR(SEARCH($A$1,$C$1:$C$4)),0,1))

This assumes the string to be looked for is in A1 and
the range to be
searched is C1:C4. If you need a case-sensitive search,
you can use FIND
instead of SEARCH.

This must be entered using Ctrl+Shift+Enter.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]

.