How can I add a wildcard to my if formula (that also contains an or condition)-CodePudding

I have a column of locations on an Excel file, and some of the locations can be named something like this

So what I want to do with my formula is say, if the last 3 characters are "IDE" or, if the last 5 characters are "IDE-(and a wildcard) then add a "Y" to the column otherwise add an "N".

I have the following formula, but even though the location is MyLocation IDE-1 it is still giving me an "N" and I'm not sure f what I am doing wrong

=IF(OR(RIGHT(L1,3)="IDE", RIGHT(L1,5)="IDE-"&"*"),"Y","N")

CodePudding user response：

Try:

Formula in B1:

=IF(SUM(COUNTIF(A1,{"* IDE","* IDE-?"})),"Y","N")

Or, a little less verbose:

=IF(SUM(COUNTIF(A1,"* IDE"&{"","-?"})),"Y","N")

CodePudding user response：

=IF(OR(RIGHT(L1,3)="IDE", IFERROR(SEARCH("IDE-*",RIGHT(L1,5),1),FALSE)),"Y","N")

Change your RIGHT(L1,5)="IDE-"&"*" to IFERROR(SEARCH("IDE-*",RIGHT(L1,5),1),FALSE) . SEARCH can use wildcards