decimals to feet and inches with excel 2002

anon125

Active Member
Joined
Feb 14, 2008
Messages
365
=B1&"' "&TEXT(C1,"0 #/##")&CHAR(34)
34.6 leads to
34' 6 49/71"

<tbody>
</tbody>
thanks all

i have forgotten how to post the spreadsheet
 
Last edited:

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,746
Office Version
2010
Platform
Windows
Or =B1 & TEXT(C1,"' 0 #/##""")

Is there a question?
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,915
Office Version
2010
Platform
Windows
Here is a UDF (user defined function) that you can consider. It rounds fractions to the nearest 64th of an inch (can be changed via the optional second argument if desired).
Code:
[table="width: 500"]
[tr]
	[td]Function FeetInches(ByVal DecimalFeet As Variant, Optional ByVal LargestDenominator As Long = 64) As String
  Dim GCD As Long
  Dim TopNumber As Long
  Dim Remainder As Long
  Dim Feet As Long
  Dim Numerator As Long
  Dim Denominator As Long
  Dim Inches As Double
  DecimalFeet = CDbl(DecimalFeet)
  Feet = Int(DecimalFeet)
  Inches = 12 * (DecimalFeet - Feet)
  Denominator = LargestDenominator
  Numerator = Format(Denominator * Abs(Inches - Int(Inches)), "0")
  If Numerator Then
    GCD = LargestDenominator
    TopNumber = Numerator
    Do
      Remainder = (GCD Mod TopNumber)
      GCD = TopNumber
      TopNumber = Remainder
    Loop Until Remainder = 0
    Numerator = Numerator \ GCD
    Denominator = Denominator \ GCD
    FeetInches = CStr(Feet) & "' " & Int(Inches) & "-" & CStr(Numerator) & "/" & CStr(Denominator) & """"
  End If
End Function[/td]
[/tr]
[/table]
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use FeetInches just like it was a built-in Excel function. For example,

=FeetInches A1)

If you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 

anon125

Active Member
Joined
Feb 14, 2008
Messages
365
Thanks Rick
i would rather keep it simple!
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,915
Office Version
2010
Platform
Windows
Thanks Rick
i would rather keep it simple!
I understand that, but if the formula 'shg' gave you does not work in your version of Excel, you may not have any other choice (which is why I posted the UDF for you to consider).
 

Watch MrExcel Video

Forum statistics

Threads
1,099,087
Messages
5,466,550
Members
406,489
Latest member
Ankusharma

This Week's Hot Topics

Top