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

N

Nastech

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

Max

=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 Insert>Name>Define

Then you could use this in the CF's formula:
=SUMPRODUCT(--ISNUMBER(FIND(MyR,A1)))>0
 
T

T. Valko

=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"))
Click to expand...

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))
 
R

Rick Rothstein \(MVP - VB\)

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
R

Rick Rothstein \(MVP - VB\)

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

Rick
 
R

Rick Rothstein \(MVP - VB\)

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
 
T

T. Valko

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)
 
R

Rick Rothstein \(MVP - VB\)

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
 
N

Nastech

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... thank
=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)
 
N

Nastech

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)
 
N

Nastech

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
 
Top