Wildcard

MRomanow

New Member
Joined
Mar 10, 2005
Messages
46
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??
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Logical functions like IF, OR, AND, and NOT do not admit wildcards (i.e., * or ?). Try instead...

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"PBX","SB","ZE"},E20:F20))),"N","Y")
 
Upvote 0
Wildcards don't work in direct string comparisons... they instead are used in Searches and Lookups. Try this instead:

IF(OR(Not(Iserror(Search("PBX",E20))),Not(Iserror(Search("SB",F20))),Not(Iserror(Search("ZE",F20)))),"N","Y")

Edit: Thanx Greg :oops: copy and paste strikes again... but I also always forget that Isnumber() will return realiable results in spite of errors, so incorporating NBVC's solution, this would be better written as:

IF(OR(Isnumber(Search("PBX",E20)),Isnumber(Search("SB",F20)),Isnumber(Search("ZE",F20))),"N","Y")

I never can get the {} groupings to work in a way I expect, so I tend to shy away from them... personal preference.
 
Upvote 0
That worked perfectly. Can you explain what it is doing so when my boss asks me I can sound smart? THANKS! :biggrin:
 
Upvote 0
Try:

=IF(OR(ISNUMBER(FIND("PBX",E20)),ISNUMBER(FIND({"SB","ZE"},F20))),"N","Y")
 
Upvote 0
Is anyone else having trouble getting Aladin's to work? Or am I just whiffing this and can't see where? It's hitting OK if matching on arg 1 or arg 2, but wants to compare "ZE" against a 3rd cell which is not in E20:F20.

<sup>edit</sup> Paul (hatman), I think you misplaced a parenthesis. I think something like so is what you had in mind:<ul>[*]=IF(OR(NOT(ISERROR(SEARCH("PBX",E1))),NOT(ISERROR(SEARCH("SB",F1))),NOT(ISERROR(SEARCH("ZE",F1)))),"N","Y")[/list] Note the change in the OR's. <sub>/edit</sub>
 
Upvote 0
Is anyone else having trouble getting Aladin's to work? Or am I just whiffing this and can't see where? It's hitting OK if matching on arg 1 or arg 2, but wants to compare "ZE" against a 3rd cell which is not in E20:F20.
...
Book4
DEFGH
19
20PBXSB60N
21
Sheet1


H20:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"PBX","SB","ZE"},E20:F20))),"N","Y")
 
Upvote 0
I think this tweak to Aladin's formula will work:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH({"PBX","SB","ZE"},E20&F20))),"N","Y")

Edit: Though now that I look at it, when working properly, it doesn't care if it finds the search strings occur in E20 or F20. All in all NBVC's solution probably gives the most bang for the buck.
 
Upvote 0

Forum statistics

Threads
1,217,381
Messages
6,136,230
Members
450,000
Latest member
jgp19

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top