Does ?? act as a wlidcard?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 23 Feb 2008 21:04 GMT

I've had a very basic formula return results I just cannot understand.

It's a simple COUNTIF(A1:A1500,C5)

C5 contains a text string. The range A1:A1500 contains people's
initials i.e JO, AL, MC etc..

But where the initals aren't known someone has input ?? (2 question
marks) in the range a1:a1500.

Now when I input ?? into C5 to get a count of cells in the range I get
returned a total count of *all* initials, and not just the two cells
containing ??

Now I can't explain this, and I've experimented and it doesn't happen
with just one ? or even three ?. It only happens with 2 question marks.
(it seems to give a count of non-blanks)

Does anyone know what's going on?

Reply to this Message

Dave Peterson - 23 Feb 2008 21:16 GMT

? is a wild card representing a single character.
* is a wild card representing any number of characters.

You use ~ as the "escape" character which tells excel to look for a real ? or
asterisk.

~? to find ?
~* to find *
~~ to find ~~

So your formula would become:
=countif(a1:a500,substitute(c5,"?","~?"))

if you really want to be careful, you should use formulas like:
=COUNTIF(A1:A500,
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"))

And this kind of formula with =vlookup():
=VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"~","~~"),"?","~?"),"*","~*"),
Sheet2!$A:$B,2,FALSE)

> I've had a very basic formula return results I just cannot understand.
>
[quoted text clipped - 15 lines]
>
> Does anyone know what's going on?

Signature

Dave Peterson

Reply to this Message

Jay - 23 Feb 2008 21:22 GMT

Thanks Dave...I could see it was acting like a wildcard, but in all my
years I've never come across using the tilde like that in xl , so thanks
for that. I've learnt something today :-)

Jason

> ? is a wild card representing a single character.
> * is a wild card representing any number of characters.
[quoted text clipped - 36 lines]
>>
>> Does anyone know what's going on?

Reply to this Message

Paul Hyett - 24 Feb 2008 09:21 GMT

>? is a wild card representing a single character.
>* is a wild card representing any number of characters.
>
>You use ~ as the "escape" character which tells excel to look for a real ? or
>asterisk.

That's a handy thing to know - thanks.

Signature

Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me)

Reply to this Message

RagDyeR - 23 Feb 2008 21:21 GMT

? is a wild card meaning *any single* character.

So, ?? means *any 2* characters.

In C5, simply enter:
~?~?
to count those ?? cells.

Signature

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Reply to this Message

RagDyeR - 23 Feb 2008 21:30 GMT

Actually,

~??

seems to work also!

Signature

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

? is a wild card meaning *any single* character.

So, ?? means *any 2* characters.

In C5, simply enter:
~?~?
to count those ?? cells.

Signature

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Reply to this Message