Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form behind cell entries | Excel Discussion (Misc queries) | |||
REPEATING TEXT ENTRIES TO COME UP WHEN TYPING FIRST LETTER | New Users to Excel | |||
create a form and find a value in one cell and replace a value in | Excel Worksheet Functions | |||
Find based on cond. 4matting? Replace as ALT-Enter? (2 diff prob | Excel Discussion (Misc queries) | |||
How do I find a cell starting with a specific letter? | Excel Discussion (Misc queries) |