you beat me back.. was trying


=IF(CC9="","",IF(LEFT(CC9,3)<"<i",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255),
--MID(LEFT(CC9,LEN(CC9)-8),SEARCH("<b<i",CC9)+3,255)))

for
11:35am - <b0.5601</b <i11:19am</i - <b<i0.16</i</b

first half works, not the second... don't get that

"Dave Peterson" wrote:

Maybe it would be better to remove the <i and <b and </i and </b from the
expression and then just look for the hyphen?

Put this kind of formula in an adjacent cell:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"< i",""),
"</i",""),"<b",""),"</b","")

(All one cell)

And then use that second suggestion to search for the hypen.

But this technique will fail after a couple more HTML tags. You only get 7
nested functions.



nastech wrote:

ps: for text / numbers:

Number / Text Combination "Custom" formatting:
- is there a way to include examples (if exist), for text / number
combinations: removing text from? etc.

----------
Great result on:
=IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH("<b",CC9)+3,255))

do not think would have got that for a while.
is there a way to "OR" the search( on "<i", trying some variations on:

=IF(CC9="","",--MID(LEFT(CC9,LEN(CC9)-4),SEARCH(OR({"<b","<i"}),CC9)+3,255))

not quite working, will do the longer way if have to

"Dave Peterson" wrote:

I'd use a helper cell to display in different powers of 10.

Format the cell as Number with the correct number of digits after the decimal
point and the displayed value won't round if you shrink the columnwidth--but you
may see ###'s if you shrink the columnwidth too much.

Do you really embed the <b and </b or is that just in the post.

If you embed them in the cell, you can pick out the stuff by using something
like:

=MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255)
if you want the number (not text)
=--MID(LEFT(A1,LEN(A1)-4),SEARCH("<b",A1)+3,255)

If you have
12:18pm - 10.63
in the cell, you could search for the hypen and extra everything to the right.

=TRIM(MID(A1,SEARCH("-",A1)+1,255))
or
=--(MID(A1,SEARCH("-",A1)+1,255))

nastech wrote:

hi! thanks for the response, sorry for long time back.. been busy.. have
been tearing sheet formula's apart to reduce size of sheet. much work but
went form 11MB to 9MB for same output of formulas. embedding was some of it,
e.g.:
instead of
=if(a1="x",hyperlink($a$2&v9),"1",if(a1="y",hyperl ink($a$3&v9),"2")) to
=hyperlink(if(a1="x",$a$2,$a$3)&v9),$a$1) not just hyperlinks..

anyways have had some grief over excel formats, custom vs formula.
for 1 column version of % results (i.e. 90, see a 9) used to use 2 columns,
worker column with 1 eq, e.g.:
=IF(OR(BJ9={"",0},BJ9<0.02),"",((CB9-BZ9)/((CA9-0.0001)-BZ9)*10))

instead of *100 for a normal 90 result, see e.g.: 9
small problem is now 13 (130%) is what is seen.

off worker column above had series of 10 formulas: if(90-.5)/10,"9" for
each level of 10, made equation 10x in size. doing so much back-forth,
question no longer seems to matter. (used to look like):

=IF(OR(BN9="",BN9<0.02),"",IF(AI9<=$BD$7,IF($BD$7< 10,$BD$7/1,$BD$7/10),
IF(OR(AI9=$BD$2,AND(AI9<(INDIRECT($BD$18)-0.5),AI9INDIRECT($BD$19))),(CE9-CC9)/((CD9-0.0001)-CC9)*$BV$19,
IF(AI9<=$BD$6,$BD$6/10,
IF(AI9<=$BD$5,$BD$5/10,
IF(AI9<=$BD$4,$BD$4/10,
IF(AI9<=$BD$3,$BD$3/10,
IF(AI9<=$BD$2,$BD$2/10))))))))

problems:
- excel formatting divide by thousands only with comma's? ,,
needed divide by 10 or 100, could not find to save life, must be a way. just
not in excel help for an example.
- no setting for do not round??? had to use alot of if(xy,(z-0.5), til
figured out to use less than <

- still have problem with character/ text combo. examples a
12:18pm - <b0.63</b
12:18pm - <b10.63</b
and pulling the number from that (0.63 or 10.63)

quickest answer see is cut / move destination of that colum other than
download "to"
and somehow left/right len or istext/isnum?. thanks (sorry for the lack of
info on previous.. )

"Dave Peterson" wrote:

Can you use multiple cells?

For instance, put your equation in A1 and then in B1:

=a1*If(a11,10,100)



nastech wrote:

060628 Embedding IF: depends on result..

Hi, is there a way to modify the following equation, to reduce from 3 legs,
e.g.:
=IF(equation1,same eq*10,same eq*100) thanks

to something like:
=equation*10(or(if<1,if10),*10 again,"") otherwise nothing

purpose
- single digit view, on narrow column, 10 (or 95) goes to # sign
custom format: #
- same in other columns with much larger equations x 3 or 4 legs will be to
much.

-----
actual equations in formula vary, and do not matter, still all (ulitmately
<1 & 10)
=IF(OR(BR9={"",0},BR9<0.02),"",IF(((CI9-CG9)/((CH9-0.0001)-CG9))1,
((CI9-CG9)/((CH9-0.0001)-CG9)*10),((CI9-CG9)/((CH9-0.0001)-CG9)*100)))

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson