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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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")
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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.
 

MRomanow

New Member
Joined
Mar 10, 2005
Messages
46
That worked perfectly. Can you explain what it is doing so when my boss asks me I can sound smart? THANKS! :biggrin:
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

Try:

=IF(OR(ISNUMBER(FIND("PBX",E20)),ISNUMBER(FIND({"SB","ZE"},F20))),"N","Y")
 

Greg Truby

MrExcel MVP
Joined
Jun 19, 2002
Messages
10,014

ADVERTISEMENT

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>
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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")
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
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.
 

Forum statistics

Threads
1,136,352
Messages
5,675,269
Members
419,558
Latest member
Ryanms

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
Top