Converting Fractions In Excel

[Gill]

Are there any software gurus out there who can tell me how to convert fractions (such as "2/7") from text in one cell into decimal in another cell (ie "0.286") using Excel?

Gill

 

[LyNx]

Yes. In the second cell, make this an = of the first cell. (place the equal sign in the second cell then left click into the first cell, ie: cell B1 formula is =A1 )

Then in the first cell "right click" and format cell and change the Category to fraction. So when you add a fraction into the first cell, it copies this into the second cell but converts to decimal numbering

Works in excel 2003

hope this helps

Andy

 

[dedee]

Gill, do you know how to convert text to columns? In the data menu select text to column, select delimited in step one, in step two select Other and enter a / in the box, in step 3 select general for both columns.
The result should leave 2 in one column and 7 in the next you can then do a simple calculation.

Hope this helps


Andy

 

[Chris Knight]

Gill,
You need to use the MID function to extract the relevant text, thus in your example if you enter the following in a cell =MID(cellref,1,1) where my term cellref refers to where your fraction 2/7 is, then in the cell where you placed the formula, you get 2 returned. If you likewise made the function =MID(cellref,3,1) you get a 7 returned.

Thus if you placed these functions in two cells (say they were A1 and A2 and then in the cell where you wanted the decimal answer, you simply placed the formula A1/A2 you will get the answer - 0.285714

You could combine all this in a single formula but I have separated it to keep it as clear as possible - and because I need another cup of coffee before doing it!

 

[LyNx]

Gill, if you add =(25.4*A1) for the formula in the second columns then this will convert from fractions (first column) to mm in second. If that is what your after.

Simple solution :wink:

Andy

 

[Chris Knight]

GILL,
Had coffee - answer is - assuming you had 2/7 as text in say cell A5 :-

=MID(A5,1,1)/MID(A5,3,1)

 

[dedee]

Well that just goes to show that there is more than one way to skin a cat or convert fractions in excel :lol:

Andy

 

[sxlalan]

This is slightly more generic as it looks for the / rather than assuming it is a certain number of characters in

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

Cheers

Alan

 

[andrewm]

GILL,
Had coffee - answer is - assuming you had 2/7 as text in say cell A5 :-

=MID(A5,1,1)/MID(A5,3,1)

But that only works if the fraction has a single digit on top and on the bottom. It will fall down for something like 15/16.

A more generic solution (again assuming that the value is in cell A5) would be:

=LEFT(A5,FIND("/",A5)-1)/RIGHT(A5,LEN(A5)-FIND("/",A5))

Gill, how are your getting your fractions into the cell in the first place? if I type 2/7 into a cell it interprets it as 02-Jul. I need to preceed it with a single quote to get it as text. I am sure that if we knew what you were trying to do there might be a more elegent solution.

Andrew

 

[andrewm]

Alan, you beat me to it.

Andrew

 

[Chris Knight]

Alan, Andrew,

You are dead right, I should have had two cups of coffee..

 

[LyNx]

if I type 2/7 into a cell it interprets it as 02-Jul.

not it you format the complete column or cells to be fractions.

then you can have 400/546 if you wanted

Andy

 

[andrewm]

not it you format the complete column or cells to be fractions.

then you can have 400/546 if you wanted

Too true. I was starting with a new sheet and didn't know what Gill was doing. Of course, Excel being ever helpful will keep 400/456 as a fraction even if it is not formatted as text, it's only if it thinks you are trying to enter a date that it will convert it. I do think that some of these programs can be too clever for their own good.

Andrew - who has spent many a day trying to get Excel (or Word) to do what he wants and not what Mr Gates thinks it should be.

 

[LyNx]

how would the equations above handle 3 3/16 into decimal.

I'm on the understanding that Gill wants to convert inches to MM??

Andy

 

[Anonymous]

Having read through all the responses I am left wondering if Gill is confused yet?

 

[Gill]

Dare I level with you guys :? ? I don't need a morality lecture.

As I'm sure some of you might be aware, there are various bookmakers on the internet who quote odds in fraction format, eg 2/7, 100/30 and so on. I want to be able to cut and paste these into a spreadsheet for further analysis; the cutting and pasting is easy but it isn't possible to work with fractions in a spreadsheet, hence the need to convert them into decimals.

I used to do this sort of stuff a few years ago and I was surprised to find that certain procedures could eliminate the bookmakers' edge. Unfortunately, I never managed to generate an edge for the punter . Still, it was fun messing about with the data and it didn't cost me any money*.

All that was quite a while ago and the spreadsheets I wrote seem to have vanished from my computers. I can't remember what I did before, it took me quite a while to learn how to use Excel because programming doesn't come easily to me. I do recall that the most difficult part of the procedure was converting the fractions into decimals. Seeing the post about splitting text into separate columns is ringing very loud bells and I believe that was how I did it in the past, using a macro. Nevertheless, I like the simplicity of being able to use formulae in cells so I'll also have a look at the other methods that have been suggested.

Many thanks for all the help, guys .

Gill

*Actually, I'm not a gambler - the last time I had a bet was in 1985 when I dropped a jackpot from a fruit machine and I resolved to quit while I was ahead .

 

[andrewm]

how would the equations above handle 3 3/16 into decimal.

Now after my first coffee of the day and a bit of further reading...

If we a talking about having to format a cell in order to enter a fraction then why not just format the cell as a fraction. Select fraction as the cell format and select up to two (or up to three) digits. then Gill can just use the value in the cell in her calculations. It will then work for things like 3 3/16.

Andrew

 

[LyNx]

Andrew. Thats how i noted it at the start :roll:

I was just wondering how it would work out for the long formula's if a whole number was added. :?

Andy

 

[andrewm]

Andrew. Thats how i noted it at the start

Sorry Andy, should have read your post a bit better. For some reason I thought you were talking about formatting a cell as Text which is what I had been doing to avoid conversion to a date. Looks like you were already one step ahead of me.

Andrew

 

[Neil]

I was just wondering how it would work out for the long formula's if a whole number was added. :?

Works fine, Andy - just leave a space between the whole number and the fraction

Cheers,
Neil