# Thread: convert fractions written as text into actual values Thanks: 0 Likes: 0

1. ## convert fractions written as text into actual values

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

2. ## Re: convert fractions written as text into actual values

try this

Sheet12
AB
14/50.8
26/41.5
33/13
42/50.4
54/50.8

Worksheet Formulas
CellFormula
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))

3. ## Re: convert fractions written as text into actual values

excellent!!

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

4. ## Re: convert fractions written as text into actual values

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[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[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]

Thank you!

Sheet12[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[/TR]
[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]

[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]
[TH]B2[/TH]
[TD]=LEFT(A2,SEARCH("/",A2)-1)/RIGHT(A2,LEN(A2)-SEARCH("/",A2))[/TD]
[TH]B3[/TH]
[TD]=LEFT(A3,SEARCH("/",A3)-1)/RIGHT(A3,LEN(A3)-SEARCH("/",A3))[/TD]
[TH]B4[/TH]
[TD]=LEFT(A4,SEARCH("/",A4)-1)/RIGHT(A4,LEN(A4)-SEARCH("/",A4))[/TD]
[TH]B5[/TH]
[TD]=LEFT(A5,SEARCH("/",A5)-1)/RIGHT(A5,LEN(A5)-SEARCH("/",A5))[/TD]
[/TD]
[/TR]

[/QUOTE]

5. ## Re: convert fractions written as text into actual values

Originally Posted by 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?

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•