"CONTAINS" operator?

D

Dave Bilger

I am using excel 2002. I want a cell to use conditional formating
whereby say IF CELL A1 CONTAINS "ORDER" THEN A2 WILL TURN A SPECIFI
COLOR.

I can easily figure this out if A1 EQUALS "ORDER", but can't figure ou
the operator or trick for CONTAINS ...

I've tried =A1="*ORDER*" but it doesn't work . . .

Can anyone help me
 
M

Marcelo

Hi Dave,

on conditional format change to the formula is and use
=isnumber(find("order",a2,1))

format as you need
hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Dave Bilger" escreveu:
 
M

Marcelo

hi Bob,

just to clarify my mind and learn more with you.

why did you use IF(isnumber....instead of isnumber?

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Bob Phillips" escreveu:
=IF(ISNUMBER(FIND("ORDER",A1)),...

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Dave Bilger" <[email protected]>
wrote in message
Click to expand...
 
H

Harlan Grove

Bob Phillips wrote...
=IF(ISNUMBER(FIND("ORDER",A1)),...
Click to expand...
....

Could be shortened to

=IF(COUNTIF(A1,"*ORDER*"),...

if case sensitivity isn't an issue, or

=IF(COUNT(FIND("ORDER",A1)),...

if case sensitivity is important, but if nesting levels are also an
issue, try

=IF(SUBSTITUTE(A1,"ORDER","")=A1,...
 
H

Harlan Grove

Bob Phillips wrote...
=IF(ISNUMBER(FIND("ORDER",A1)),...
Click to expand...
....

Could be shortened to

=IF(COUNTIF(A1,"*ORDER*"),...

if case sensitivity isn't an issue, or

=IF(COUNT(FIND("ORDER",A1)),...

if case sensitivity is important, but if nesting levels are also an
issue, try

=IF(SUBSTITUTE(A1,"ORDER","")<>A1,...
 
B

Bob Phillips

Hi Marcelo,

I just assumed that the OP would want to take some action based upon the
test, not that he just wanted True/False.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
D

Dave Bilger

Marcelo,

Thank you (and all the rest of you).
This hint does actually work great >>>

=isnumber(find("order",a2,1))

(This was the only one which works of all the suggestions)

Is there a way to make it not case sensitive?

Thanks !

Colorado Dave :)

===============
 
P

Pete_UK

Use SEARCH instead of FIND.

Pete

Dave said:
Marcelo,

Thank you (and all the rest of you).
This hint does actually work great >>>

=isnumber(find("order",a2,1))

(This was the only one which works of all the suggestions)

Is there a way to make it not case sensitive?

Thanks !

Colorado Dave :)

===============
Click to expand...
 
D

Dave Bilger

Hey, Pete_UK,

the SEARCH part works !

Thanks a million to you and the rest !!!

Genius Group . . .

Colorado Dave :)
 
Top