Wildcard Searches in IF Formulas

[Jay]

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

 

[T. Valko]

=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)&"*")

 

[Jay]

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

 

[Roger Govier]

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),"")&"*")