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

Attached Files

text_odds_convert.xls (13.5 KB, 443 views)

ive corrected this file to show the total return

Attached Files

text_odds_convert_2.xls (13.5 KB, 495 views)

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.

__________________
RaceCensus - powerful system testing software.
Now with over 400,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 30/04/2024
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg

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.