Using a wildcard in an 'If' formula

[Simon Topping]

Hi,

I have used the '*' wildcard with lookup formulae with no
problems however when I try to use them with and 'If'
formula, it doesn't work?

The equation below returns the correct value:

=IF(A20="No Contract - TPM","True","False")

however the following doesn't work:

=IF(A20="*TPM","True","False")

Is there something I'm doing wrong or is it just not
possible to use with an 'If' formula?


Thanks

Simon

 

[Paul]

Hi,

I have used the '*' wildcard with lookup formulae with no
problems however when I try to use them with and 'If'
formula, it doesn't work?

The equation below returns the correct value:

=IF(A20="No Contract - TPM","True","False")

however the following doesn't work:

=IF(A20="*TPM","True","False")

Is there something I'm doing wrong or is it just not
possible to use with an 'If' formula?


Thanks

Simon

You could use
=IF(RIGHT(A20,3)="TPM","True","False")

 

[Peo Sjoblom]

Or the less error prone

=ISNUMBER(FIND("TPM",A20))

 

[Robert]

Have you tried the other wildcard character "?".

 

[Dave Peterson]

And use SEARCH instead of FIND if that TPM can be upper/lower case.

And one more alternative:

=if(COUNTIF(A1,"*tpm*"),true,false)

 

[Simon Topping]

Thanks everyone - your help was really useful.

s