Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: convert fractions written as text into actual values

  1. #1
    Board Regular
    Join Date
    Apr 2006
    Location
    London
    Posts
    331
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP sanrv1f's Avatar
    Join Date
    Jan 2009
    Location
    Chennai, India
    Posts
    3,468
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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))

    regards
    Sankar
    show your sheet on the board, get HTML maker


    யாதும் ஊரே, யாவரும் கேளிர்; தீதும் நன்றும் பிறர்தர வாரா

  3. #3
    Board Regular
    Join Date
    Apr 2006
    Location
    London
    Posts
    331
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert fractions written as text into actual values

    excellent!!

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

  4. #4
    New Member
    Join Date
    Feb 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #5
    New Member
    Join Date
    May 2017
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: convert fractions written as text into actual values

    Quote Originally Posted by Plato0n View Post
    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..

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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