Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wildcard Character in IF formulas | Excel Discussion (Misc queries) | |||
Wildcard Searches across Worksheets | Excel Worksheet Functions | |||
using the wildcard ? in formulas | Excel Discussion (Misc queries) | |||
Getting valid web searches and avoiding sites that contaminate web searches | New Users to Excel | |||
wildcard use in formulas | Excel Worksheet Functions |