Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 671
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Jay Jay is offline
external usenet poster
 
Posts: 671
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,480
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Wildcard Character in IF formulas lightbulb Excel Discussion (Misc queries) 2 July 25th 08 02:53 PM
Wildcard Searches across Worksheets [email protected] Excel Worksheet Functions 1 January 26th 08 12:34 AM
using the wildcard ? in formulas Harold Good Excel Discussion (Misc queries) 1 June 27th 06 04:33 PM
Getting valid web searches and avoiding sites that contaminate web searches David McRitchie New Users to Excel 1 June 13th 06 03:00 AM
wildcard use in formulas Tobias Excel Worksheet Functions 2 August 25th 05 02:28 PM


All times are GMT +1. The time now is 11:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"