+ Reply to Thread
Results 1 to 5 of 5

Convert fraction formated numbers to text in excel

Hybrid View

Tijazap Convert fraction formated... 04-07-2012, 05:16 PM
FDibbins Re: Convert fraction formated... 04-07-2012, 05:28 PM
Tijazap Re: Convert fraction formated... 04-07-2012, 05:41 PM
mahju Re: Convert fraction formated... 04-08-2012, 02:23 PM
FDibbins Re: Convert fraction formated... 04-08-2012, 03:18 PM
  1. 04-07-2012, 05:16 PM #1
    Tijazap is offline
    Registered User
    Join Date
    04-07-2012
    Location
    A long way from where ever you are
    MS-Off Ver
    Excel 2007
    Posts
    2

    Unhappy Convert fraction formated numbers to text in excel

    Imagine I have these cells:

    A B C D
    1 1366 768 1 7/9

    What I want is to display this in D1 as either 16/9 or 16:9 ; an improper fraction or ratio
    When C1=A1/B1 and is formated as a fraction it appears as shown above in the table.
    If I could parse out the formated text in C1 ("1 7/9") to cells where E1 = 1, F1= 7, and G1 =9, Then I could do this: D1= G1*E1+F1&":"&G1 but I can't see how that is possible.

    Is there another way to express the result of =A1/B1 as an improper fraction or least common denominator ratio?

    Any Ideas?
    Reply With QuoteRegister To Reply
  2. 04-07-2012, 05:28 PM #2
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Convert fraction formated numbers to text in excel

    Go into cell formatting, select custom formatting, and type in ???/?? Add/remove "?" as needed
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 04-07-2012, 05:41 PM #3
    Tijazap is offline
    Registered User
    Join Date
    04-07-2012
    Location
    A long way from where ever you are
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Convert fraction formated numbers to text in excel

    Okay so I see, continuing to research, that just changing to a custom format of ##/## will solve the improper fraction part of the question!

    I'd still like to know,
    Is there a way to display =A1/B1 as "16:9"??
    Last edited by Tijazap; 04-07-2012 at 08:27 PM.
    Reply With QuoteRegister To Reply
  4. 04-08-2012, 02:23 PM #4
    mahju is offline
    Valued Forum Contributor mahju's Avatar
    Join Date
    11-27-2010
    Location
    Pakistan, Faisalabad
    MS-Off Ver
    Excel 2010 plus
    Posts
    730

    Re: Convert fraction formated numbers to text in excel

    Hi

    Check the method i used

    B1 and c1 contains fraction part

    Divide the numbers (D1)

    Get the integer (E1)

    Get the remainder (F1)
    =B5-C5*E5
    Get the fraction part (Fraction format)
    =f5/c5
    Parse the G5 to get the required (H5)
    =RIGHT(TEXT(G5,"?/?"),1)*E5+LEFT(TEXT(G5,"?/?"),1) & "/" & RIGHT(TEXT(G5,"?/?"),1)
    You can make a bigger formula of these steps

    See attached for example

    Regards
    Attached Files Attached Files
    Mark the thread as solved if you are satisfied with the answer.


    In your first post under the thread tools.

    Mahju
    Reply With QuoteRegister To Reply
  5. 04-08-2012, 03:18 PM #5
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Convert fraction formated numbers to text in excel

    perhaps you missed what i said above?

    Go into cell formatting, select custom formatting, and type in ???/?? Add/remove "?" as needed
    Last edited by FDibbins; 04-08-2012 at 05:53 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1