Converting decmial feet to foot inches with defined fractions

[wagmanm]

I would like to find a formula to take an answer I get that is in the decimal form of feet, ex. 6.104' and have it converted to 6' 1 1/4" or 6 ft 1 1/4 in.
I have this formula:

=INT(B12) & " ft " & TEXT(MOD(B12,1)*12, "# ??/?? ""in""")
Which works well however I do not want fractions smaller than 1/8 or maybe 1/16

so I changed the formula to this:

=INT(B12) & " ft " & TEXT(MOD(B12,1)*12, "# ??/8 ""in""")
Which also works but then i always get my fraction over 8. ex. 6' 1 4/8" instead of seeing 1/2"

Also if the is no fraction in the answer I get very weird spacing with this formula.

I found this code for doing what I want to do I believe but I am not very familiar with using code in developer, I have made some buttons with codes so I do understand a little. When I enter this code in I cannot figure out how to have it pull from the cell where my answer is output, and run the code, and then output the answer to the cell I would like.

Below I have posted this code I have found. If anyone can help me with either of these options of obtaining my answer in this form I would really appreciate it.



Public Function LenText(FeetIn As Double)
' This function will change a decimal number of feet to the text string
' representation of feet, inches, and fractional inches.
' It will round the fractional inches to the nearest 1/x where x is the denominator.
' Copyright 1999 MrExcel.com
Denominator = 32 ' must be 2, 4, 8, 16, 32, 64, 128, etc.
NbrFeet = Fix(FeetIn)
InchIn = (FeetIn - NbrFeet) * 12
NbrInches = Fix(InchIn)
FracIn = (InchIn - NbrInches) * Denominator
Numerator = Application.WorksheetFunction.Round(FracIn, 0)
If Numerator = 0 Then
FracText = ""
ElseIf InchIn >= (11 + (31.4999999 / 32)) Then
NbrFeet = NbrFeet + 1
NbrInches = 0
FracText = ""
ElseIf Numerator = Denominator Then
NbrInches = NbrInches + 1
FracText = ""
Else
Do
' If the numerator is even, divide both numerator and divisor by 2
If Numerator = Application.WorksheetFunction.Even(Numerator) Then
Numerator = Numerator / 2
Denominator = Denominator / 2
Else
FracText = " " & Numerator & "/" & Denominator
Exit Do
End If
Loop
End If
LenText = NbrFeet & "' " & NbrInches & FracText & """"
End Function

 

[taurean]

It is a function so you have to:
1. Copy this code to a standard module. See below if you don't know how to go about it.
Excel 2010 - Overview of Visual Basic Editor - MacroMountain.com - YouTube

2. Then paste the code and come back to Excel.

3. You can use it like any other function in Excel.
e.g. =LenText(6.104)

 

[Andrew Poulsom]

You can use the function on your worksheet like this:

=LenText(A1)

where A1 contains a number like 6.104.

 

[wagmanm]

Thank you guys very much this was a huge help and now I got this to work, I belive I was putting the code in the wrong spot and I needed to insert a module.

Would anyone know how to have it return a number that is rounded to an 1/8" I would prefer this that getting fractions such as 3/32"?

 

[wagmanm]

I was able to figure this out on my own, incase anyone else see this and wants to be able to do this, you just need to change the denomonater to whatever you would like either 4 , 8 ,16 and then change the 31.49999 to either 3.4999, 7.49999, or 15.4999.

Thanks everyone

 

[Rick Rothstein]

I was able to figure this out on my own, incase anyone else see this and wants to be able to do this, you just need to change the denomonater to whatever you would like either 4 , 8 ,16 and then change the 31.49999 to either 3.4999, 7.49999, or 15.4999.

Thanks everyone

Below is another function you can use. It allows you to specify the maximum denominator to be used via the optional second argument; so, if you wanted all answers with a denominator of 16 or less, you would call the function like this...

=FeetInches(A1,16)

If the value can be reduced to an exact fraction with a lower denominator, it will be, otherwise the fractional value will be rounded to the nearest numerator that works with the maximum denominator you specify. The maximum denominator argument is optional and defaults to 64 if omitted.

Function FeetInches(ByVal DecimalNumber As Variant, Optional ByVal LargestDenominator As Long = 64) As String
   Dim GCD As Long, TopNumber As Long, Remainder As Long
   Dim WholeNumber As Long, Numerator As Long, Denominator As Long
   If IsNumeric(DecimalNumber) Then
      DecimalNumber = CDbl(DecimalNumber)
      WholeNumber = Fix(DecimalNumber)
      Denominator = LargestDenominator
      Numerator = Format(Denominator * Abs(DecimalNumber - WholeNumber), "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(WholeNumber) & "' " & CStr(Numerator) & "/" & CStr(Denominator) & """"
      End If
   End If
End Function

 

[bijitsony]

Hey this is great.

I am not sure how to do it but I don't want denominator, instead I want inches to be displayed as normal 1" to 12".
Can you please guide me on this?

 

[Rick Rothstein]

I am not sure how to do it but I don't want denominator, instead I want inches to be displayed as normal 1" to 12".

Delete the function I gave you earlier (there is a mistake in it) and replace it with the following two function. You will call the FeetInches function (it calls the MakeFraction function as needed) passing the floating point value for the number of feet.

[table="width: 500"]
[tr]
	[td]Function FeetInches(ByVal DecimalFeet As Variant) As String
  Dim Feet As Long, Inches As Double, FractionInches As String, Fraction As String
  Fraction = MakeFraction(DecimalFeet, 64)
  Feet = Val(Fraction)
  Inches = 12 * Evaluate(Mid(Fraction, InStr(Fraction, "-") + 1))
  FractionInches = MakeFraction(Inches, 192)
  FeetInches = Feet & "' " & FractionInches & """"
End Function

Function MakeFraction(ByVal DecimalNumber As Variant, ByVal LargestDenominator As Long) As String
  Dim GCD As Long, TopNumber As Long, Remainder As Long, WholeNumber As Long, Numerator As Long, Denominator As Long
  If IsNumeric(DecimalNumber) Then
    DecimalNumber = CDbl(DecimalNumber)
    WholeNumber = Fix(DecimalNumber)
    Denominator = LargestDenominator
    Numerator = Format(Denominator * Abs(DecimalNumber - WholeNumber), "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
      MakeFraction = CStr(WholeNumber) & "-" & CStr(Numerator) & "/" & CStr(Denominator)
    End If
  End If
End Function[/td]
[/tr]
[/table]

 

[bijitsony]

Thanks alot for your support!