Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
text to number
I import data onto Excel from a horseracing database.
The odds of the horses are expressed in the form 3/1, 15/8, 9/4 etc etc . I have to import these as text otherwise they become dates. They are therefore on the LHS of the cell. I don't know how to convert these to single numbers (decimals) in order to make calculations, and following the process 'text to numbers' on Excel does not seem to achieve any result - possibly because of the / Help please |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
text to number
Hi,
It's a little clumsy, but this will convert your text fraction to a decimal. LEFT(A1,FIND("/",A1)-1)/RIGHT(A1,LEN(A1)-FIND("/",A1)) Dave "colensa" wrote: I import data onto Excel from a horseracing database. The odds of the horses are expressed in the form 3/1, 15/8, 9/4 etc etc . I have to import these as text otherwise they become dates. They are therefore on the LHS of the cell. I don't know how to convert these to single numbers (decimals) in order to make calculations, and following the process 'text to numbers' on Excel does not seem to achieve any result - possibly because of the / Help please |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
text to number
I don't know how to convert these to single numbers (decimals)
For example do you want to convert 15/8 to 15.8 or 15 divided to 8? -- R. Khoshravan Please click "Yes" if it is helpful. "colensa" wrote: I import data onto Excel from a horseracing database. The odds of the horses are expressed in the form 3/1, 15/8, 9/4 etc etc . I have to import these as text otherwise they become dates. They are therefore on the LHS of the cell. I don't know how to convert these to single numbers (decimals) in order to make calculations, and following the process 'text to numbers' on Excel does not seem to achieve any result - possibly because of the / Help please |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
text to number
Hi
One way. Assuming your text odds are in column C. InsertNameDefine Name Odds Refers to =EVALUATE($C1) Change the column letter in the formula to suit the column where your text values appear. On any row, enter into a cell =Odds and you will see the result turned into Value:1 15/8 will become 1.875, 9/4 will become 2.25 -- Regards Roger Govier "colensa" wrote in message ... I import data onto Excel from a horseracing database. The odds of the horses are expressed in the form 3/1, 15/8, 9/4 etc etc . I have to import these as text otherwise they become dates. They are therefore on the LHS of the cell. I don't know how to convert these to single numbers (decimals) in order to make calculations, and following the process 'text to numbers' on Excel does not seem to achieve any result - possibly because of the / Help please |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
text to number
You have the answer but it is possible to split these expressions into two
columns by using the 'text to columns' facility. Having imported data to col A (formatted as text) - highlight col A - select text to columns - field type=delimited - next - select 'other' having deselected all other options - input / in box - tick box 'treat conseq etc' - text qualifier should be * - data preview pane should default both columns to general - selecting finish will leave 15 in Col A and place 8 in col B. Just for future info and to confirm that the / is no bar to this. Cheers "colensa" wrote: I import data onto Excel from a horseracing database. The odds of the horses are expressed in the form 3/1, 15/8, 9/4 etc etc . I have to import these as text otherwise they become dates. They are therefore on the LHS of the cell. I don't know how to convert these to single numbers (decimals) in order to make calculations, and following the process 'text to numbers' on Excel does not seem to achieve any result - possibly because of the / Help please |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
text to number
Thanks for your response. I think I have solved the problem now
"Khoshravan" wrote: I don't know how to convert these to single numbers (decimals) For example do you want to convert 15/8 to 15.8 or 15 divided to 8? -- R. Khoshravan Please click "Yes" if it is helpful. "colensa" wrote: I import data onto Excel from a horseracing database. The odds of the horses are expressed in the form 3/1, 15/8, 9/4 etc etc . I have to import these as text otherwise they become dates. They are therefore on the LHS of the cell. I don't know how to convert these to single numbers (decimals) in order to make calculations, and following the process 'text to numbers' on Excel does not seem to achieve any result - possibly because of the / Help please |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to change scientific number to regular number or text | Excel Discussion (Misc queries) | |||
Counting a mixed text/number column based on text in another colum | Excel Discussion (Misc queries) | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
Why does this fail? =TEXT(RC3,Number)&" / "&TEXT(R32C,Number) | Excel Worksheet Functions | |||
not able to convert text, or graphic number to regular number in e | Excel Worksheet Functions |