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

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

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

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

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

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