search within a range of cells?

[jasondebolt]

I'm looking for an existing excel function or UDF that will search for
a text string within a range of cells. For example, if the string in
cell A1 is "my string" I would like to know if this string exists
within a range of cells (B1:B4), and if so how many times. It's
similar to countif, but the string that will be searched is in a
different cell (A1).

A1="my string"

B1="this is my string"
B2="this is not my string"
B3="this string is not it"
B4="string, strings, 123"

I would like a function to tell me how many times "my string" shows up
in the range B1:B4. (2 times)

I'm basically looking for a UDF equiviliant to the "find and replace"
feature in excel.

I've tried using COUNTIF, DCOUNT, SEARCH, and the FIND function
without any luck.

Please help! Thanks!!

Jason

 

[Guest]

Jason,

You can use COUNTIF.. try:

=COUNTIF(B1:B4, "*" & A1 & "*")

 

[T. Valko]

COUNTIF might be adequate in this case but this is a little more robust:

=SUMPRODUCT(--(ISNUMBER(SEARCH(" "&A1&" "," "&B1:B4&" "))))

Less of a chance for "false positives" but still not "bulletproof".

COUNTIF will count:

my strings
my stringer

SUMPRODUCT *won't* count:

my string, strings, 123

But COUNTIF will

Also, does the OP want to count multiple instances in a cell:

my string is my string

Biff