Converting minus to forward slash = incorrect results

[danpai]

Hi all.

I have copied and pasted sports statistics from a website that tracks number of made basketball shots versus number of total shots. For example 5-10 is supposed to indicate the player made five of ten shots or 50%.

So I can go ahead and set these cells to a format of number or percentage, but when I attempt to convert the minus sign to a forward slash via find and replace, Excel attempts to calculate the value, but somehow totally screws up! So, in our 5/10 example, Excel (2007) turns this value into 3994300% or 39943.00.

Note that in Openoffice Calc, when I select the value of the cell, each value is proceeded by an apostrophe. When I remove the apostrophe, Calc does the same thing. If I enter "=5/10" in either program, the value is calculated as expected.

Any ideas?

Thanks in advanced,

Dan

[DonkeyOte]

Excel is being helpful and sees 5/10 as 10th May 2009... Dates in XL are integers and the integer value of 10th May 2009 is 39943.

I would suggest you leave your initial data as is (eg '5-10) and do a calculation in an adjacent column

If we assume A1 holds '5-10 then you can use the following in B1 (quick fix):

=LEFT(A1,FIND("-",A1&"-")-1)/MID(A1,FIND("-",A1&"-")+1,100)

Copy down as necessary.