Excel Help
 

I'm sure that this question has been asked before on this forum, but I want to convert text into numbers, e.g. I have SP data that is stored as text such as 100/1, 20/1 etc which I wish to convert to a numeric format.

Can anyone help please.

im sure there are other ways to do this but this seems to do ok

ive corrected this file to show the total return

Thanks Davez, Didn't quite work out Ok due to prices like 4/5F, 4/11F and 3/1E etc but I was able to work around those. Appreciated.

Just as an aside Davez, how would go about handling odds which are a mixture of numbers & letters such as 4/5F or 4/11F - can they be turned from text into numeric?

Hi La Mer,

Have you tried using related access tables?

I have all the values and corresponding decimal equivalents in one table, then import my excel files into a related table.

The query then substitutes the old value to the decimal odds.

Thanks for the advice Chrome - one problem, I don't have Access but will keep the information you stated above in the back of my mind for future reference.

A1 = 4/11f (say)
B1 =IF(RIGHT(A1,1)>"9",LEFT(A1,LEN(A1)-1),A1)
C1 =FIND("/",B1)
D1 =LEFT(B1,C1-1)/RIGHT(B1,LEN(B1)-C1)+1


Hardly my bag, but above should work for stated conditions.

B1 strips off any 1 non-numeric on right

looks like jfc gets the chocolates

In Excel you can do a find and replace. Find "/1" and replace it with nothing.

Then run another column with a formula (format cell to currency) that adds one to the number on left.

Therefore 100/1 has become 100 then in next column $101.00

For prices such as 5/2, 7/4 etc, you figure these manually, do a sort and do a copy and a mass copy into those cells.