Wildcard Searches in IF Formulas
 

I want to count incidences of a text snippet w/in longer strings in a column
of cells. I have tried =COUNTIF(K$1:K$144,"*"&$A148&"*"), where cell A148
contains the text (e.g., A148 = acct mgt) to be counted, but this misses many
occurrences. The formula works fine when I type in the text to be counted
(e.g., COUNTIF(K$1:K$144,"*acct mgt*"), but this does not permit fast
replication of the formula for different text searches. The problem seems to
be that the formula does not allow the double quotes (") on the start and end
of the search criteria. How can I set up the formula to search for text
referenced in another cell? Thx, J

Wildcard Searches in IF Formulas
 

=COUNTIF(K$1:K$144,"*"&$A148&"*")

There's nothing wrong with that formula so something else is causing it to
fail. There may be leading/trailing spaces in cell A148: _acct mgt_

See if this makes a difference:

=COUNTIF(K$1:K$144,"*"&TRIM($A148)&"*")

--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
I want to count incidences of a text snippet w/in longer strings in a
column
of cells. I have tried =COUNTIF(K$1:K$144,"*"&$A148&"*"), where cell A148
contains the text (e.g., A148 = acct mgt) to be counted, but this misses
many
occurrences. The formula works fine when I type in the text to be counted
(e.g., COUNTIF(K$1:K$144,"*acct mgt*"), but this does not permit fast
replication of the formula for different text searches. The problem seems
to
be that the formula does not allow the double quotes (") on the start and
end
of the search criteria. How can I set up the formula to search for text
referenced in another cell? Thx, J

Wildcard Searches in IF Formulas
 

Thank you, but this did not solve the problem. It appears that the search
string must include the double quotes (") on either end:

"*acct mgt*"

The "*"&$A148&"*" expression drops the quotes, though:

"*"&$A148&"*" = *acct mgt* (cell A148 = acct mgt)

I tried inserting the quotes into search string as follows, but that didn't
work either:

""*"&$A148&"*""

Am I off track here? Please help. Thx, J

"T. Valko" wrote:

=COUNTIF(K$1:K$144,"*"&$A148&"*")

There's nothing wrong with that formula so something else is causing it to
fail. There may be leading/trailing spaces in cell A148: _acct mgt_

See if this makes a difference:

=COUNTIF(K$1:K$144,"*"&TRIM($A148)&"*")

--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
I want to count incidences of a text snippet w/in longer strings in a
column
of cells. I have tried =COUNTIF(K$1:K$144,"*"&$A148&"*"), where cell A148
contains the text (e.g., A148 = acct mgt) to be counted, but this misses
many
occurrences. The formula works fine when I type in the text to be counted
(e.g., COUNTIF(K$1:K$144,"*acct mgt*"), but this does not permit fast
replication of the formula for different text searches. The problem seems
to
be that the formula does not allow the double quotes (") on the start and
end
of the search criteria. How can I set up the formula to search for text
referenced in another cell? Thx, J

Wildcard Searches in IF Formulas
 

Hi Jay
It appears that the search
string must include the double quotes (") on either end:

Not so.
"*"&$A148&"*" works absolutely fine.
The problem must be with your cell A148
Maybe there is a non-breaking space in there (Char(160))

Try
=COUNTIF(K$1:K$144,"*"&SUBSTITUTE(TRIM(A148),CHAR( 160),"")&"*")

--
Regards
Roger Govier

"Jay" wrote in message
...
Thank you, but this did not solve the problem. It appears that the search
string must include the double quotes (") on either end:

"*acct mgt*"

The "*"&$A148&"*" expression drops the quotes, though:

"*"&$A148&"*" = *acct mgt* (cell A148 = acct mgt)

I tried inserting the quotes into search string as follows, but that
didn't
work either:

""*"&$A148&"*""

Am I off track here? Please help. Thx, J

"T. Valko" wrote:

=COUNTIF(K$1:K$144,"*"&$A148&"*")

There's nothing wrong with that formula so something else is causing it
to
fail. There may be leading/trailing spaces in cell A148: _acct mgt_

See if this makes a difference:

=COUNTIF(K$1:K$144,"*"&TRIM($A148)&"*")

--
Biff
Microsoft Excel MVP


"Jay" wrote in message
...
I want to count incidences of a text snippet w/in longer strings in a
column
of cells. I have tried =COUNTIF(K$1:K$144,"*"&$A148&"*"), where cell
A148
contains the text (e.g., A148 = acct mgt) to be counted, but this
misses
many
occurrences. The formula works fine when I type in the text to be
counted
(e.g., COUNTIF(K$1:K$144,"*acct mgt*"), but this does not permit fast
replication of the formula for different text searches. The problem
seems
to
be that the formula does not allow the double quotes (") on the start
and
end
of the search criteria. How can I set up the formula to search for
text
referenced in another cell? Thx, J