"Decimal Inches" to "Total Inches & Fraction" Formula
Results 1 to 8 of 8

Thread: "Decimal Inches" to "Total Inches & Fraction" Formula

  1. #1
    New Member XxCALLofBOOTYxX's Avatar
    Join Date
    Jan 2009
    Location
    Lexington, Kentucky
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question "Decimal Inches" to "Total Inches & Fraction" Formula

    Below is a formula I use in Google sheets however it does not work for excel.

    Example: 144.3750 decimal inches would convert to 144 3/8" via the Google sheets formula. And that's what I need this formula to do but in excel I just don't know what changes need to be made.

    Code:
    =int(E10)&if(mod(E10,1)," "&round(mod(E10,1)*power(10,len(mod(E10,1)&"")-2)/gcd(mod(E10,1)*power(10,len(mod(E10,1)&"")-2),power(10,len(mod(E10,1)&"")-2)))&"/"&power(10,len(mod(E10,1)&"")-2)/gcd(mod(E10,1)*power(10,len(mod(E10,1)&"")-2),power(10,len(mod(E10,1)&"")-2)),"")&""""

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    803
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Decimal Inches" to "Total Inches & Fraction" Formula

    Hi,

    with a small fix ....

    =INT(E10)&IF(MOD(E10,1)," "&ROUND(MOD(E10,1)*POWER(10,LEN(MOD(E10,1)&"")-2)/GCD(MOD(E10,1)*POWER(10,LEN(MOD(E10,1)&"")-2),POWER(10,LEN(MOD(E10,1)&"")-2)),)&"/"&POWER(10,LEN(MOD(E10,1)&"")-2)/GCD(MOD(E10,1)*POWER(10,LEN(MOD(E10,1)&"")-2),POWER(10,LEN(MOD(E10,1)&"")-2)),"")&""""

  3. #3
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,663
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: "Decimal Inches" to "Total Inches & Fraction" Formula

    Try
    =TEXT(A1,"0 ?/?")&""""

    or you could just change the number format of A1.

  4. #4
    New Member XxCALLofBOOTYxX's Avatar
    Join Date
    Jan 2009
    Location
    Lexington, Kentucky
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Decimal Inches" to "Total Inches & Fraction" Formula

    Thank you very much admiral. you made my day a lot easier!
    Quote Originally Posted by admiral100 View Post
    Hi,

    with a small fix ....

    =INT(E10)&IF(MOD(E10,1)," "&ROUND(MOD(E10,1)*POWER(10,LEN(MOD(E10,1)&"")-2)/GCD(MOD(E10,1)*POWER(10,LEN(MOD(E10,1)&"")-2),POWER(10,LEN(MOD(E10,1)&"")-2)),)&"/"&POWER(10,LEN(MOD(E10,1)&"")-2)/GCD(MOD(E10,1)*POWER(10,LEN(MOD(E10,1)&"")-2),POWER(10,LEN(MOD(E10,1)&"")-2)),"")&""""

  5. #5
    New Member XxCALLofBOOTYxX's Avatar
    Join Date
    Jan 2009
    Location
    Lexington, Kentucky
    Posts
    39
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "Decimal Inches" to "Total Inches & Fraction" Formula

    I changed the format of the number at first but could not use the inch symbol with it. Thanks for reply still though. much appreciated.
    Quote Originally Posted by mikerickson View Post
    Try
    =TEXT(A1,"0 ?/?")&""""

    or you could just change the number format of A1.

  6. #6
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,663
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: "Decimal Inches" to "Total Inches & Fraction" Formula

    Still the TEXT formula is less typing and less use of calculation resources than concatenating the arithmetic.

  7. #7
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    5,622
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    1 Thread(s)

    Default Re: "Decimal Inches" to "Total Inches & Fraction" Formula

    Quote Originally Posted by XxCALLofBOOTYxX View Post
    I changed the format of the number at first but could not use the inch symbol with it.
    You could try a custom format pattern like this to include the inch symbol:

    Code:
    0 ?/?\"
    [code]your code[/code]

  8. #8
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    21,663
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    3 Thread(s)

    Default Re: "Decimal Inches" to "Total Inches & Fraction" Formula

    AHH! Backslash is the key character. Thanks FormR

User Tag List

Tags for this Thread

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
  •