an expression in IF theory that works based on cells containing part (not whole)of a value

hi

this is a really little thing, but I just cant find the answer anywhere

our company initial is TP, but sometimes it might be combined with a reference number, e.g TP5659
I would like the IF formula to stick pick this up..

I know if it was just the phrase 'TP' I would use =If(a1="tp"...
how do I express that I would like my If formula to work when something 'contains', not an 'exact match'

You can do it like this:

=IF(ISNUMBER(SEARCH("tp",A1)), ... etc.

SEARCH is not case-sensitive (use FIND instead if that is important to you), and the formula will return a TRUE result if the letters tp occur anywhere in A1.

You can also use COUNTIF with wildcard characters:

=IF(COUNTIF(A1,"tp*")>0, ... etc

or

=IF(COUNTIF(A1,"*tp*")>0, ... etc

The first one will only find tp if it is at the beginning of A1, whereas for the second one tp could occur anywhere.

Hope this helps.

Pete

Hay.

both of these worked a treat.
you people make it look SO easy,
thanks for your help!!