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