Excel Help

[La Mer]

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.

[davez]

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

[davez]

ive corrected this file to show the total return

[La Mer]

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.

[La Mer]

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?

[Chrome Prince]

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.

[La Mer]

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.

[jfc]

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?

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

[davez]

looks like jfc gets the chocolates

[Eddie Woloschek]

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.
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.

[La Mer]

Thanks to JFC & everyone else who offered adivce, much appreciated.