Wildcard

[MRomanow]

Can I use a wildcard in a formula looking at numerical values?

F(OR(E20="PBX",F20="SB*",F20="ZE*"),"N","Y"))

This is the fomula looking at SB43 through SB60 and ZE20 through ZE30 and I cannot get it to work. Any ideas??

 

[NBVC]

What if the OP has a "ZE" or "SB" in E20.... would he/she want an "N"..... judging by the original post...not necessarily....hence my version above.

 

[Aladin Akyurek]

Aladin,

Shouldn't H22 be "N"?

[see Greg's exhibit]

...

Greg,

I see what you mean... My humble thanks.

Something like:

=IF(SUM(COUNTIF(E20:F20,{"PBX","SB","ZE"}&"*")),"N","Y")

 

[Greg Truby]

What if the OP has a "ZE" or "SB" in E20.... would he/she want an "N"..... judging by the original post...not necessarily....hence my version above.
-NBVC

Yeah, not so fast fancypants, I hadn't gotten to you yet... you overdid it, actually , per the OP, we didn't need to wildcard the PBX test, i.e.<ul>[*]=IF(OR("PBX"=E20,ISNUMBER(FIND({"SB","ZE"},F20))),"N","Y")[/list] would have worked.

Aladin,

• You thanking me??? I'm so indebted to you that I cannot even think of an appropriate analogy...
• Thanks for that formula because I was wracking my brain trying to overcome the weakness of that FIND()/SEARCH() wildcard test in that we really needed that to return 1 to be testing for SB* and not *SB*.

 

[NBVC]

What if the OP has a "ZE" or "SB" in E20.... would he/she want an "N"..... judging by the original post...not necessarily....hence my version above.
-NBVC

Yeah, not so fast fancypants, I hadn't gotten to you yet... you overdid it, actually , per the OP, we didn't need to wildcard the PBX test, i.e.<ul>[*]=IF(OR("PBX"=E20,ISNUMBER(FIND({"SB","ZE"},F20))),"N","Y")[/list] would have worked.

Well, there you go then...I gave them a bonus...so whether it's on it's own or part of a string... it'll work.... The other offering will assume the same for that string.

 

[Greg Truby]

NBVC, while I like yours (though I would have used SEARCH, not FIND due to FIND's case sensitivity); I have to give the nod to our guru on this one since it can be more easily adapted to avoid yielding false positives on column F values like "ZZE30" or "ASB40" i.e. <ul>[*]=IF(SUM(COUNTIF(E20:F20,{"PBX","SB*","ZE*"})),"N","Y")[/list]

 

[NBVC]

NBVC, while I like yours (though I would have used SEARCH, not FIND due to FIND's case sensitivity); I have to give the nod to our guru on this one since it can be more easily adapted to avoid yielding false positives on column F values like "ZZE30" or "ASB40" i.e. <ul>[*]=IF(SUM(COUNTIF(E20:F20,{"PBX","SB*","ZE*"})),"N","Y")[/list]

That's cool...

I guess I used FIND because the OP's samples were all caps... this would assist in keeping entries consistant....but I probably should have stated that fact.

Thanks Greg.

 

[hatman]

Someday I might be able to come up with stuff as slick as you guys ... whenever I come up with something I think is robust, one of you guys (rightfully) shows a better more robust way of doing it , and I feel like a rube again

I'll keep trying, though :wink:

 

[Greg Truby]

...whenever I come up with something I think is robust, one of you guys (rightfully) shows a better more robust way of doing it , and I feel like a rube again I'll keep trying, though :wink:
-hatman

Do you have any inkling as to how many times Aladin, Fairwinds or Barry [Houdini] have done the same to me?! For a while I thought Barry was stalking me! And Aladin? 9 times out of 10 there's some interesting little tweak that makes his much more robust than whatever I'd come up with; but I'm too thick to see it; so I'll ask and Mr. Akyurek will kindly explain to me the scenarios that I had not taken into consideration. So definitely keep trying and do not take it personally! AA outwits us all; routinely.