Wildcard If Statment

[leighmetcalfe]

I'm struggling to use a wildcard with an if statement.

my formula is "=IF(OR(BZ1="99",BZ1="99"&"*"),"True","False")"

I need it to check the cell and if it has either 99 or variations of 99- then be true.

 

[FormR]

Hi, if you want to test that it stats with 99 you could try:

=IF(LEFT(BZ1,2)="99","True","False")

 

[leighmetcalfe]

Worked Perfect.

Thank you.

 

[alpadem]

Another solutioun might be =IF(BZ1="99*"),"True","False")

 

[FormR]

Another solutioun might be =IF(BZ1="99*"),"True","False")

Hi, but you can't use wildcards like that in IF() statements, hence the workaround.

 

[alpadem]

Yes, you are right, my mistake

 

[jtakw]

Hi,

Or, if you want to return the Logical TRUE/FALSE rather then the Text "True", "False":

=LEFT(BZ1,2)="99"

 

[mole999]

I had need of a wild card came up with this > =IF(COUNTIF(S3:S3,"*A/S*"),1,IF(COUNTIF(S3:S3,"*HOSPITAL*"),1,IF(COUNTIF(S3:S3,"*UNKNOWN*"),1,IF(COUNTIF(S3:S3,"*NRECORDE*"),1,""))))

no idea if that is good practice or not, everything else I found was functions

 

[jtakw]

Hi mole999,

I'm making a couple of assumptions here:

1. You're looking in a Single cell (S3 for this sample)
2. You Don't want Partial strings like AA/SS, HOSPITALIZED, NRECORDED, etc. "mis" Counted

If point 2 above is correct, I would add a Space in front and after the Text string to be searched, so that the formula will only result True for those specific words:

=IF(ISNUMBER(LOOKUP(2,1/SEARCH({" A/S "," HOSPITAL "," UNKNOWN "," NRECORDE "}," "&S3&" "))),1,"")

 

[mole999]

thats far neater