in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is there
another way or something doing wrong? thanks.

=OR(FIND(CM9,"H"),FIND(CM9,"X"))

Something like this will work in the CF:
=OR(ISNUMBER(FIND("H",A1)),ISNUMBER(FIND("X",A1)))

Alternatively, if you have a lot of FINDs to do,
just create* a defined range, eg: MyR
to refer to, eg: ={"H";"X";"Z"}
*via InsertNameDefine

Then you could use this in the CF's formula:
=SUMPRODUCT(--ISNUMBER(FIND(MyR,A1)))0
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nastech" wrote:
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is there
another way or something doing wrong? thanks.

hi, the defined name seems to work well for convience.. wonder if there is a
shorter way for the formula for a true/ false.. my formula seems to be
getting longer as I go. info as follows:

would think there would be a shorter way, especially in a defined name..
example working on

=IF(SUMPRODUCT(--ISNUMBER(FIND(L,CO9)))0,

would think defined name could be as an "OR" ?

for: CO9=L

(L: defined has multiple chars: ={"T";"X";"Y";"Z"}
where T responds, but not any of XYZ for CO9=L


the following is just getting longer & longer... thanks
=IF(ISNUMBER(CP748),CP748+IF(SUMPRODUCT(--ISNUMBER(FIND(L,CO748)))0,IF(SUMPRODUCT(--ISNUMBER(FIND(preA,CQ748)))0,2,IF(SUMPRODUCT(--ISNUMBER(FIND(wav2,CR748)))0,1)),0),0)



"Max" wrote:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))

Something like this will work in the CF:
=OR(ISNUMBER(FIND("H",A1)),ISNUMBER(FIND("X",A1)))

Alternatively, if you have a lot of FINDs to do,
just create* a defined range, eg: MyR
to refer to, eg: ={"H";"X";"Z"}
*via InsertNameDefine

Then you could use this in the CF's formula:
=SUMPRODUCT(--ISNUMBER(FIND(MyR,A1)))0
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nastech" wrote:
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is there
another way or something doing wrong? thanks.

trying some variation, is there a good idea here somewhere?

=SEARCH(OR({"H","X"}),CQ747)
=FIND(CQ747,OR({"H","X"}))
=SEARCH({"H","X"},CQ747)
=FIND(CQ747,{"H","X"})
=SEARCH(L,CQ747)
=FIND(CQ747,L)



"Max" wrote:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))

Something like this will work in the CF:
=OR(ISNUMBER(FIND("H",A1)),ISNUMBER(FIND("X",A1)))

Alternatively, if you have a lot of FINDs to do,
just create* a defined range, eg: MyR
to refer to, eg: ={"H";"X";"Z"}
*via InsertNameDefine

Then you could use this in the CF's formula:
=SUMPRODUCT(--ISNUMBER(FIND(MyR,A1)))0
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nastech" wrote:
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is there
another way or something doing wrong? thanks.

ANSWER: ?? if interpret correctly, reversing the defined name to the 1st
position, think allows for benefits of search, such as:

(W is a defined name with multiple entries, e.g.: (case sens)
={"bd";"bot";"top";"lvg";"uad";"ud";"dd";"BD";"BOT ";"TOP";"LVG";"UD";"DD"}

=SUMPRODUCT(--ISNUMBER(FIND(W,L9:M9)))0

"Max" wrote:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))

Something like this will work in the CF:
=OR(ISNUMBER(FIND("H",A1)),ISNUMBER(FIND("X",A1)))

Alternatively, if you have a lot of FINDs to do,
just create* a defined range, eg: MyR
to refer to, eg: ={"H";"X";"Z"}
*via InsertNameDefine

Then you could use this in the CF's formula:
=SUMPRODUCT(--ISNUMBER(FIND(MyR,A1)))0
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Nastech" wrote:
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is there
another way or something doing wrong? thanks.

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

If you're *specifically* looking at the first and last characters you can
save 4 keystrokes:

=OR(LEFT(CM9)="h",RIGHT(CM9)="h")

If the character can be anywhere in the string and is not case specific:

=SEARCH("h",CM9)

to something like:
=OR(FIND(CM9,"H"),FIND(CM9,"X"))

You have the arguments in the wrong order.

Since you're looking for different characters it's not going to be shorter.
Also note that FIND is case sensitive. H does not match h and X does not
match x.

=COUNT(FIND("H",CM9),FIND("X",CM9))

If you don't need it to be case sensitive then it'll be a little longer:

=COUNT(SEARCH("H",CM9),SEARCH("X",CM9))


--
Biff
Microsoft Excel MVP


"Nastech" wrote in message
...
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is
there
another way or something doing wrong? thanks.

What about this? For your first conditional formula...

=ISNUMBER(SEARCH("h*h",A1))

and for your second conditional formula...

=ISNUMBER(SEARCH("h*x",A1))

Note, both of these are case insensitive.

Rick


"Nastech" wrote in message
...
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is
there
another way or something doing wrong? thanks.

The OP was using OR, not AND, Rick.
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
What about this? For your first conditional formula...

=ISNUMBER(SEARCH("h*h",A1))

and for your second conditional formula...

=ISNUMBER(SEARCH("h*x",A1))

Note, both of these are case insensitive.

Rick


"Nastech" wrote in message
...
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is
there
another way or something doing wrong? thanks.

At 5:12 in the morning, just before going to sleep after apparently dozing
on and off for awhile (as a result of a 5-hour ride back from seeing my son
earlier on), that is not how I saw it.<g Thanks for pointing that out to
me.

Rick


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
The OP was using OR, not AND, Rick.
--
David Biddulph

"Rick Rothstein (MVP - VB)" wrote in
message ...
What about this? For your first conditional formula...

=ISNUMBER(SEARCH("h*h",A1))

and for your second conditional formula...

=ISNUMBER(SEARCH("h*x",A1))

Note, both of these are case insensitive.

Rick


"Nastech" wrote in message
...
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is
there
another way or something doing wrong? thanks.

For your first conditional formula...
=ISNUMBER(SEARCH("h*h",A1))

That will find false positives like:

thigh
thorough
haha
happy birthday


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
What about this? For your first conditional formula...

=ISNUMBER(SEARCH("h*h",A1))

and for your second conditional formula...

=ISNUMBER(SEARCH("h*x",A1))

Note, both of these are case insensitive.

Rick


"Nastech" wrote in message
...
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is
there
another way or something doing wrong? thanks.

True enough... and I'll use the same excuse I gave to David in his
sub-thread for missing that fact.<g

Rick


"T. Valko" wrote in message
...
For your first conditional formula...
=ISNUMBER(SEARCH("h*h",A1))

That will find false positives like:

thigh
thorough
haha
happy birthday


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
What about this? For your first conditional formula...

=ISNUMBER(SEARCH("h*h",A1))

and for your second conditional formula...

=ISNUMBER(SEARCH("h*x",A1))

Note, both of these are case insensitive.

Rick


"Nastech" wrote in message
...
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is
there
another way or something doing wrong? thanks.

Of course, as David pointed out, my faulty formula was attempting to answer
the wrong question; but, had the question actually been to match the outer
two characters, I think this formula would have worked correctly...

=REPLACE(A1,2,LEN(A1)-2,"")="hh"

Assuming that a case insensitive match was desired.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
True enough... and I'll use the same excuse I gave to David in his
sub-thread for missing that fact.<g

Rick


"T. Valko" wrote in message
...
For your first conditional formula...
=ISNUMBER(SEARCH("h*h",A1))

That will find false positives like:

thigh
thorough
haha
happy birthday


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
What about this? For your first conditional formula...

=ISNUMBER(SEARCH("h*h",A1))

and for your second conditional formula...

=ISNUMBER(SEARCH("h*x",A1))

Note, both of these are case insensitive.

Rick


"Nastech" wrote in message
...
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is
there
another way or something doing wrong? thanks.

Yeah, that'll work...

....but the OR version is 1 character shorter! <g

Speaking of shorter...

The pedantic approach is to use ISNUMBER:

ISNUMBER(MATCH(...))
ISNUMBER(SEARCH(...))
ISNUMBER(FIND(...))

Lately, I've been moving away from ISNUMBER to COUNT (where applicable)


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote in
message ...
Of course, as David pointed out, my faulty formula was attempting to
answer the wrong question; but, had the question actually been to match
the outer two characters, I think this formula would have worked
correctly...

=REPLACE(A1,2,LEN(A1)-2,"")="hh"

Assuming that a case insensitive match was desired.

Rick


"Rick Rothstein (MVP - VB)" wrote in
message ...
True enough... and I'll use the same excuse I gave to David in his
sub-thread for missing that fact.<g

Rick


"T. Valko" wrote in message
...
For your first conditional formula...
=ISNUMBER(SEARCH("h*h",A1))

That will find false positives like:

thigh
thorough
haha
happy birthday


--
Biff
Microsoft Excel MVP


"Rick Rothstein (MVP - VB)" wrote
in message ...
What about this? For your first conditional formula...

=ISNUMBER(SEARCH("h*h",A1))

and for your second conditional formula...

=ISNUMBER(SEARCH("h*x",A1))

Note, both of these are case insensitive.

Rick


"Nastech" wrote in message
...
in Conditional Format, trying to shorten many examples of:

=OR(LEFT(CM9,1)="h",RIGHT(CM9,1)="h")

to something like:

=OR(FIND(CM9,"H"),FIND(CM9,"X"))
but cannot get multiple items to be valid in a conditional format. is
there
another way or something doing wrong? thanks.