FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

=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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

=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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

Ooops!

Disregard this:

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

I guess that means it's time for some nourishment! I must consume mass
quantities!


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

LOL... had me recount the formulas twice to make sure I hadn't screwed up
the count. Besides being one character shorter, the REPLACE version also has
one fewer function call than the OR version (although I'm not sure of the
relative efficiency between OR/LEFT/RIGHT as compared to REPLACE/LEN).

Rick


"T. Valko" wrote in message
...
Ooops!

Disregard this:

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

I guess that means it's time for some nourishment! I must consume mass
quantities!


--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.

FIND LETTER IN CELL (cond. form mult entries not wrking)
 

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.