Excel Wildcard Quirk Solved

Last week I wrote about using less than and greater than operators with letters like this:

=COUNTIF(grade,"<B*")

Here’s the post.

Whilst playing around with different formulas I noticed that wildcards were not behaving as I would expect.

For example using the data below with cells A2:A13 named ‘grade’:

Formula:

=COUNTIF(grade,"<B")

=2 which is counting the two A grades.

And this formula:

=COUNTIF(grade,"<B*")

=6 which is counting the A and B grades.

But this formula:

=COUNTIF(grade,">B*")

=6 which is the two C grades + two D grades + two E grades, but it doesn’t include the four B grades.

I was puzzled because with less than =COUNTIF(grade,"<B*") it included B grades, but with greater than =COUNTIF(grade,">B*") it didn’t include B grades.

The wildcard didn’t seem to be working consistently.

And the reason is this; sometimes¹ when we use greater than or less than operators to test strings the asterisk is not considered a wildcard, it’s simply text, and as text it has a character value of 42.

We can see its character value in the Insert Symbol dialog box:

We can also see that B has a character value of 66:

These examples best illustrate what is happening behind the scenes:

Note: Excel is testing the first character value, then the second character value and so on. It isn’t giving B* a value of 6,642 (six thousand, six hundred and forty-two).

1.Earlier I said "sometimes when we use greater than or less than operators to test strings the asterisk is not considered a wildcard", asterisks used like this; =COUNTIF(grade,"<>B*") where <> is not equal to, or this; =COUNTIF(grade,"=B*") are treated as wildcards.

It’s only with these operator arrangements that the asterisk is treated as text:

>=

<=

>

<

A big thank you to Roberto Mensa for explaining this quirk.

If you liked this please share it by using the buttons below to Like it on Facebook, Tweet about it on Twitter, share it on Google+ or LinkedIn, or just leave a comment thanking Roberto.