Formula to Convert Number with Decimals to feet and inches including fractions (1 Viewer)

Tophan

Registered User.
Local time
Today, 13:41
Joined
Mar 27, 2011
Messages
362
Good Morning,

Is there a formula to convert a number to feet and inches with fractions. For example I would like to convert 143.625 to 11 ft 11 5/8 in.

I was using this formula - =INT(CELLREF/12) & " ft. " & MOD(CELLREF,12) & " in." but can't find a way to include for fractions.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:41
Joined
May 21, 2018
Messages
8,463
Just did this in another thread
Code:
Public Function DecInchesToString(ByVal DecimalInches As Double, Optional ShowZeros As Boolean = True) As String
   Dim Feet As Long
   Dim Inches As Long
   Dim Remainder As Long
   Dim strFeet As String
   Dim strInches As String
   Dim strFraction As String
      
   Feet = DecimalInches \ 12
   Inches = Int(DecimalInches - (Feet * 12))
   strFraction = GetFractionInches(DecimalInches)
   'This will show 0 feet and 0 inches when set to true
   If ShowZeros Then
     If Not strFraction = "" Then strFraction = "-" & strFraction
     DecInchesToString = Feet & " Feet " & Inches & strFraction & " Inches"
   'This will not show units that are 0 value. 0 Feet 1-1/2 inch would show as simply 1-1/2 inches
   Else
     If Feet = 0 Then
       strFeet = ""
     Else
       strFeet = Feet & " Feet"
     End If
     If Inches <> 0 And strFraction <> "" Then
        strInches = Inches & "-" & strFraction & " Inches"
     ElseIf Inches <> 0 And strFraction = "" Then
        strInches = Inches & " Inches"
     ElseIf Inches = 0 And strFraction <> "" Then
        strInches = strFraction & " Inches"
     End If
     DecInchesToString = Trim(strFeet & " " & strInches)
   End If
End Function


Public Function GetFractionInches(ByVal DecimalInches As Double) As String
  Dim Denominator As String
  Dim Numerator As String
  Dim The64th As Double
  Dim The32nd As Double
  Dim The16th As Double
  Dim The8th As Double
  Dim TheQtr As Double
  Dim TheHalf As Double
  'Code assumes 64th is smallest unit of measure
  If DecimalInches - Int(DecimalInches) = 0 Then
    Exit Function
  End If
  DecimalInches = DecimalInches - Int(DecimalInches)
  The64th = Int(64 * DecimalInches)
  Denominator = "64"
  Numerator = The64th
  The32nd = 32 * DecimalInches
  If The32nd = Int(The32nd) Then
      Denominator = "32"
      Numerator = The32nd
      The16th = 16 * DecimalInches
      If The16th = Int(The16th) Then
        Denominator = "16"
        Numerator = The16th
        The8th = 8 * DecimalInches
        If The8th = Int(The8th) Then
           Denominator = "8"
           Numerator = The8th
           TheQtr = 4 * DecimalInches
           If TheQtr = Int(TheQtr) Then
              Denominator = "4"
              Numerator = TheQtr
              TheHalf = 2 * DecimalInches
              If TheHalf = Int(TheHalf) Then
                Denominator = "2"
                Numerator = TheHalf
              End If
           End If
        End If
      End If
  End If
  GetFractionInches = Numerator & "/" & Denominator
End Function

Public Sub TestGetString()
  Dim theDecimal As Double
  theDecimal = 12 * 6
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
  theDecimal = 12 * 6 + 11
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
  theDecimal = 12 * 6 + 11 + 11 / 32
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
  theDecimal = 11 + 11 / 32
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
  theDecimal = 12 * 6 + 11 / 32
  Debug.Print DecInchesToString(theDecimal)
  Debug.Print DecInchesToString(theDecimal, False)
End SubODE]
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:41
Joined
May 21, 2018
Messages
8,463
If you did not run it here is the output of my test
Code:
6 Feet 0 Inches
6 Feet
6 Feet 11 Inches
6 Feet 11 Inches
6 Feet 11-11/32 Inches
6 Feet 11-11/32 Inches
0 Feet 11-11/32 Inches
11-11/32 Inches
6 Feet 0-11/32 Inches
6 Feet 11/32 Inches

The demo shows that you can choose to show the 0 value units. Example
6 Feet 0-11/32 Inches
6 Feet 11/32 Inches
 

Tophan

Registered User.
Local time
Today, 13:41
Joined
Mar 27, 2011
Messages
362
Thanks again...it's a little too complex for my level. Did some more searching and modified my original formula and found something that is easier for me and (so far) is giving the results I was looking for.

=INT(MROUND(CELLREF,1/16)/12) & "' " & TEXT(MOD(MROUND(CELLREF,1/16),12),"#-#/##")
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:41
Joined
May 21, 2018
Messages
8,463
it's a little too complex for my level
Its a function. Do not have to understand it, you can simply use it. Just drop it in a standard module. Then simply

= DecInchesToString(CELLREF)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:41
Joined
May 7, 2009
Messages
19,169
majp its excel si you have to firther convert it using range or cell.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 13:41
Joined
May 21, 2018
Messages
8,463
No it works fine in excel
=DecInchesToString(someCellReference)
 

Users who are viewing this thread

Top Bottom