Countif fields with **
It's tough to guess why anyone does anything, huh?
(I still don't buy it <vvbg.)
"T. Valko" wrote:
I don't see how case sensitivity would make a
difference with those wildcards
If you start with the premise that FIND is case sensitive for a reason:
A1 = abcd
FIND("ab?d",A1)
FIND("ab*",A1)
Would work on abXd or abxd. The wildcards don't take case into consideration
which is contrary to the functionality of FIND.
--
Biff
Microsoft Excel MVP
"Dave Peterson" wrote in message
...
I'd guess it was the second--or maybe it was written by the same person.
=Find() before lunch and =search() after lunch.
I don't see how case sensitivity would make a difference with those
wildcards,
though. I do understand how a lunch break would make me smarter!
"T. Valko" wrote:
=search() (but not =find() for some strange reason???).
I suspect that reason is because FIND is case sensitive.
Although it may be that 2 different programmers wrote the code for those
2
functions.
--
Biff
Microsoft Excel MVP
"Dave Peterson" wrote in message
...
Yep. It occurs in =sumif(), =countif(), =match(), =vlookup(),
=search()
(but
not =find() for some strange reason???). And maybe more(???).
Excel supports wild cards:
* = any string of characters
? = any one character
and the way you tell excel that you want to use an asterisk is to
prefix
it with
~ (~*).
And since that ~ is a special character, you have to tell excel to use
two
when
you want to use 1. ~ becomes ~~.
So this could be an =vlookup() formula that "fixes" all 3 of those
special
characters:
=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~"," ~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)
So...
In your formula, you could use:
=COUNTIF($I$5:$I$13,SUBSTITUTE(I5,"*","~*"))
But if there's a chance that any of those strings contain *, ? or ~,
you'd
want
to use:
=COUNTIF($I$5:$I$13,
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(I5,"~","~~"),"?", "~?"),"*","~*"))
(all one cell)
Jerry wrote:
I have had an inconsistent problem using Countif (Excel 2003) to find
duplicates in a column. I do not wish to remove them just count the
number
of times the item shows in the list.
The data column looks like this.
ID # Countif (Duplicate)
BW-CA-46**94-2227626 1
BW-CA-4614-*94-2227626 1
BW-CA-4**94-0358460 3
BW-CA-4213**94-1009696 1
BW-CA-4258**94-1009696 1
BW-CA-4505**94-0358460 1
BW-CA-4808--94-0358460 1
The Countif uses this =COUNTIF($I$5:$I$13,I5)
The first item also counts the second and the third counts the last
two.
To
confuse me the 4th and fith do not count each other. When I replace
the
**
with -- I get the correct result. Any Idea why the * or ** cause
this
problem and does it occur in other functions.
--
Jerry
--
Dave Peterson
--
Dave Peterson
--
Dave Peterson
|