excel question?

Question

20: 5- 6- 4 appears in one cell. Need formula to do the following:

(5*10+6*7+4*3)/20 and put answer in another cell.

Thanks

101:14-26-11
37: 7-10- 3
64: 7- 9- 6
79:14-22-11
26: 6- 9- 1
39: 5- 0- 5
35: 8- 2- 6
19: 3- 3- 1
22: 4- 3- 4
75: 7- 3- 7

the above are further examples of horse racing form.
1st number=number of starts
2nd number=number of wins
3rd number=number of seconds
4th number=number of thirds
the "-" between the numbers is a spacer and not a negative reference.
each line appears in one cell only and needs an answer in one cell only.
thanks for efforts so far....
will look into but don't think so....so far
will need to cut and paste list of these lines from A1:A24 and formula will put corresponding answers in B1:B24

thanks....hope this clarifies things....

Answer 1

We need to use the excel function "find". Since there are two "-"'s, we use find to find part of the expression, and then find the "-" in that part of the expression. Enter the values in column A. Enter this formula in B1 and drag down;


=(10*(MID(A1,FIND(":",A1)
+1,FIND("-",A1)-1
-FIND(":",A1)))
+7*MID(A1,FIND("-",A1)
+1,FIND("-",A1,
FIND("-",A1)+1)-
FIND("-",A1)-1)
+3*RIGHT(A1,LEN(A1)
-(FIND("-",A1,FIND("-",A1)
+1))))/(LEFT(A1,FIND
(":",A1)-1))
This is one big formula. I just broke it up so the textbox could handle it.

Answer 2

"5- 6- 4"
let the cell reference be Xn:
In any cell where you can still see Xn while working, enter

=(10*val(left$(Xn,1) + 7*val(mid$(Xn,4,1)) + 3*val(right$(Xn,1))/20

If your string is "20: 5- 6- 4", you will need to use mid$( , , ) twice, once for the "5" and once for the "6"

Move this cell to its desired location.
If you copy it to another cell, make sure you fix the Xn by using $X$n.

Note I'm not sure of the Excel value function (It could be anything from "V" to "Value". check your function list for correct syntax. I'm also not sure if Excel starts counting from 0 or 1, which, so the value "4" above may have to be changed to "3" to get a correct result.

Just read your add'l info.
in cell B1 enter
=(10*val(left$(A1,1) + 7*val(mid$(A1,4,1)) + 3*val(right$(A1,1))/20
Use "fill down" to copy down to B24 In this case it's not necessary to fix cell references.
or use the copy function.

Answer 3

FIRST PUT THIS EXPRESSION AS =(5*10+6*7+4*3)/20
INTO CELL A1, THEN WRITE IN CELL D9 THE EXPRESSION =A1
U'LL SEE THE SAME 5.2
or did mean something else?