Does ?? act as a wlidcard?

[Jay]

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?

 

[Dave Peterson]

? 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)

 

[RagDyeR]

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

So, ?? means *any 2* characters.

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

HTH,

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

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?

 

[Jay]

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

 

[RagDyeR]

Actually,

~??

seems to work also!
--

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.
--

HTH,

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

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?

 

[Paul Hyett]

? 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.