FIND 1 char in cell of any 3 char =True

  • Thread starter Thread starter Nastech
  • Start date Start date
N

Nastech

080425 FIND 1 char in cell of any 3 char =True

been having trouble figuring out SEARCH & FIND, trying to find if a
character in a cell is present. do not see any TRUE/FALSE examples, just a
bunch of errors.

finally have following work in a cond. format:
=SEARCH(A9,$A$2)

where only 1 character/number exists in A9, and multiple chars in $A$2

now need to test A9 if has "H" for e.g.: HLA
actually need to test for multiple characters in A9, e.g.: "X","H"
would use fixed cell to test for, to save space
would use smaller functions to save space, FIND instead of SEARCH, but not
critical
thanks
 
oops, may have spoke too soon, found some material to study, but if these
don't apply feel free to suggest, thanks; If this covers what looking for
(was going to expand on having complete letter groups tested for in a cell..
eg: bd, bsd, spd)

found material:
=OR(FIND(CM9,"H"),FIND(CM9,"X")) for 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

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

=ISNUMBER(SEARCH("h*h",A1)) and for your second conditional formula...
=ISNUMBER(SEARCH("h*x",A1)) Note, both of these are case insensitive.
For your first conditional formula... >=ISNUMBER(SEARCH("h*h",A1)) That will find false positives like: thigh thorough haha happy birthday
Click to expand...





________________
 
=SUMPRODUCT(--(ISNUMBER(FIND({"X","H"},A26))))>0

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
hi, have been using this example & works, but would think there would be a
shorter way of using it, 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


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)
 
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)
 
If you define L as a defined name with a refers to value of
={"T";"X";"Y";"Z"} that will work, or put those values in a set of cells and
name those cells as L.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top