decimals to feet and inches with excel 2002

[anon125]

=B1&"' "&TEXT(C1,"0 #/##")&CHAR(34)
34.6 leads to

<tbody>
[TD="class: xl24"]34' 6 49/71"[/TD]

</tbody>

thanks all

i have forgotten how to post the spreadsheet

 

[shg]

Or =B1 & TEXT(C1,"' 0 #/##""")

Is there a question?

 

[anon125]

Thanks
in 2002 it says VALUE

 

[Rick Rothstein]

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

[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]

Thanks Rick
i would rather keep it simple!

 

[Rick Rothstein]

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

 

[anon125]

thanks for your help