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

Jason,

You can use COUNTIF.. try:

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


--
Hope that helps.

Vergel Adriano


" wrote:

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

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

"Vergel Adriano" wrote in message
...
Jason,

You can use COUNTIF.. try:

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


--
Hope that helps.

Vergel Adriano


" wrote:

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