convert fractions written as text into actual values

[jpowell79]

Hi guys,

would really appreciate anyones help with this.

I have a column full of text-formatted fractions...

4/5
6/4
3/1
2/5
4/5

etc, etc.....the column is very long!!

I need to convert these to actual fractions...ones I can add to equations, allowing me to multiply and add.

If I highlight the column and goto "format Cells" and change to fraction, the values still stay in the left of the cell.....its only when I manually click on each individual cell, then tick the little green tick, does the cell validate, and the value in the cell moves to the right (and is therefore a proper value).....problem is I have to keep doing this for each individual cell.....and I have 70000+ cells in this column!

Any suggestions on how I could speed this up?

thanks

 

[sanrv1f]

try this

Excel Workbook
	A	B
1	4/5	0.8
2	6/4	1.5
3	3/1	3
4	2/5	0.4
5	4/5	0.8
Sheet12

Cell Formulas
Range	Formula
B1	=LEFT(A1,SEARCH("/",A1)-1)/RIGHT(A1,LEN(A1)-SEARCH("/",A1))
B2	=LEFT(A2,SEARCH("/",A2)-1)/RIGHT(A2,LEN(A2)-SEARCH("/",A2))
B3	=LEFT(A3,SEARCH("/",A3)-1)/RIGHT(A3,LEN(A3)-SEARCH("/",A3))
B4	=LEFT(A4,SEARCH("/",A4)-1)/RIGHT(A4,LEN(A4)-SEARCH("/",A4))
B5	=LEFT(A5,SEARCH("/",A5)-1)/RIGHT(A5,LEN(A5)-SEARCH("/",A5))

 

[jpowell79]

excellent!!

thankyou.....you just saved me 2 hours of my life

 

[Plato0n]

I know this is an old thread but I'm wondering if there is a universal formula regardless if there is a whole number before the fraction or not. For example, in the formula that you have provided, it only works when there isn't a whole number included. In a scenerio like the one below, would there be a universal formula that I can apply that would handle both without and without whole numbers?

Sheet12<table class="html-maker-worksheet" border="1" cellspacing="0" cellpadding="0" verdana,="" arial,="" tahoma,="" calibri,="" geneva,="" sans-serif;"="" width=""><thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]</thead><tbody>[TR]
[TH]1[/TH]
[TD]1 4/5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]6/4[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]3/1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]12 2/5[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]4/5[/TD]
[TD="align: right"]0.8

[/TD]
[/TR]</tbody></table>
Thank you!

Sheet12<table class="html-maker-worksheet" border="1" cellspacing="0" cellpadding="0" width=""><thead>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]</thead><tbody>[TR]
[TH]1[/TH]
[TD]4/5[/TD]
[TD="align: right"]0.8[/TD]
[/TR]
[TR]
[TH]2[/TH]
[TD]6/4[/TD]
[TD="align: right"]1.5[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD]3/1[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD]2/5[/TD]
[TD="align: right"]0.4[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD]4/5[/TD]
[TD="align: right"]0.8[/TD]
[/TR]</tbody></table><table width=""><tbody>[TR]
[TD="bgcolor: #FFFFFF"]Worksheet Formulas<table class="html-maker-worksheet" border="1" cellspacing="0" cellpadding="0" width=""><thead><tr>[TH]Cell[/TH]
[TH]Formula[/TH]</tr></thead><tbody><tr>[TH]B1[/TH]
[TD]=LEFT(A1,SEARCH("/",A1)-1)/RIGHT(A1,LEN(A1)-SEARCH("/",A1))[/TD]</tr><tr>[TH]B2[/TH]
[TD]=LEFT(A2,SEARCH("/",A2)-1)/RIGHT(A2,LEN(A2)-SEARCH("/",A2))[/TD]</tr><tr>[TH]B3[/TH]
[TD]=LEFT(A3,SEARCH("/",A3)-1)/RIGHT(A3,LEN(A3)-SEARCH("/",A3))[/TD]</tr><tr>[TH]B4[/TH]
[TD]=LEFT(A4,SEARCH("/",A4)-1)/RIGHT(A4,LEN(A4)-SEARCH("/",A4))[/TD]</tr><tr>[TH]B5[/TH]
[TD]=LEFT(A5,SEARCH("/",A5)-1)/RIGHT(A5,LEN(A5)-SEARCH("/",A5))[/TD]</tr></tbody></table>[/TD]
[/TR]</tbody></table>
[/QUOTE]

 

[ExcelUser4j]

I know this is an old thread but I'm wondering if there is a universal formula regardless if there is a whole number before the fraction or not. For example, in the formula that you have provided, it only works when there isn't a whole number included. In a scenerio like the one below, would there be a universal formula that I can apply that would handle both without and without whole numbers?


[TR]
[TD="bgcolor: #FFFFFF"]Worksheet Formulas[TH]Cell[/TH]
[TH]Formula[/TH]
[TH]B1[/TH]
[TD]=LEFT(A1,SEARCH("/",A1)-1)/RIGHT(A1,LEN(A1)-SEARCH("/",A1))[/TD]
[/TD]
[/TR]

[/QUOTE]

If the fraction takes the form XX YY/ZZ then the Value() function does the work, so all you need to do is determine whether XX is present, in which case there would be a space in the text value. Here is what i used for the fix:

=IF(ISERROR(SEARCH(" ",A1)),LEFT(A1,SEARCH("/",A1)-1)/RIGHT(A1,LEN(A1)-SEARCH("/",A1)),VALUE(A1))

The result is a decimal value.

Hope this helps..