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

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

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

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

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

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