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

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

1. ## "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. ## 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. ## Re: "Decimal Inches" to "Total Inches & Fraction" Formula

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

or you could just change the number format of A1.

4. ## Re: "Decimal Inches" to "Total Inches & Fraction" Formula

Thank you very much admiral. you made my day a lot easier!
Originally Posted by admiral100
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. ## 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.
Originally Posted by mikerickson
Try
=TEXT(A1,"0 ?/?")&""""

or you could just change the number format of A1.

6. ## 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. ## Re: "Decimal Inches" to "Total Inches & Fraction" Formula

Originally Posted by XxCALLofBOOTYxX
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 ?/?\"`

8. ## Re: "Decimal Inches" to "Total Inches & Fraction" Formula

AHH! Backslash is the key character. Thanks FormR

## 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
•