Below are two shorter formulas that I posted against the OP's later thread
in this same newsgroup (where he asked how to change the cell references in
the formula); I am posting the exact message here that I used in my response
to the later thread. I thought it best to repeat the formulas here for the
Google archives. Note that at the time I originally posted them against the
later thread, I wasn't aware this thread existed which should, in part,
explain the wording I used in my message. Here is that response...
If, as I suspect, the only non-digits that would appear in A2 and B2 are the
fractional symbols with ASCII/ANSI codes of 188, 189 or 190, then you can
use this much shorter formula to do what your posted formula does...
=IF(ISNUMBER(A2),A2,LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2))-187)/4)-IF(ISNUMBER(B2),B2,LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2))-187)/4)
Of course this suffers from repeated use of the A2 and B2 references. The
following formula, while longer than the above formula but still much
shorter than yours, has the benefit that the A2 and B2 references are used
only once each...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(188),".25"),CHAR(189),".50"),CHAR(190),".75")-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,CHAR(188),".25"),CHAR(189),".5"),CHAR(190),".75")
Rick
Jim,
those values you have are text values - the characters ¼, ½ and even ¾
have codes of 188, 189 and 190 respectively. So if you have numbers
with those fractional values in columns A and B and you want to
subtract them, you will need to split out the integer and fractional
parts. Assume you have a series of such numbers starting in A2, then
put this formula in C2:
=IF(AND(CODE(RIGHT(A2,1))>=188,CODE(RIGHT(A2,1))<=190),
1*LEFT(A2,LEN(A2)-1)+(CODE(RIGHT(A2,1))-187)/4,A2)-
IF(AND(CODE(RIGHT(B2,1))>=188,CODE(RIGHT(B2,1))<=190),
1*LEFT(B2,LEN(B2)-1)+(CODE(RIGHT(B2,1))-187)/4,B2)
and then you can copy this down column C. Here is the result of some
test values I tried out:
6½ 1¼ 5.25
1¼ 3½ -2.25
3½ 1½ 2.00
3½ 2½ 1.00
3½ 1¼ 2.25
6½ 1¼ 5.25
6 3½ 2.50
6½ 4¾ 1.75
6½ 4 2.50
4¾ 3½ 1.25
4¾ 1¼ 3.50
Of course, this subtracts B from A, but if you want it the other way
round you can swap over the references to A2 and B2 (or a quicker way
would be to change the - to a + in the middle of the formula before
the second IF and put a - in front of the first IF).
You can see that this copes with B being larger than A (2nd example),
and it also copes with one (or both) numbers not having fractional
values. It also copes with one of the numbers being expressed as 4.8,
for example. As can be seen, it doesn't convert a result of .25, .50
or .75 back into the respective fraction - I've formatted column C as
a number with 2 dp.
Hope this helps.
Pete
