' It will round the fractional inches to the nearest 1/x where x is the denominator.ĭenominator = 32 ' must be 2, 4, 8, 16, 32, 64, 128, etc.įracIn = (InchIn - NbrInches) * Denominator ' representation of feet, inches, and fractional inches. ' This function will change a decimal number of feet to the text string Public Function LenText(FeetIn As Double) First word is inchesįeet = feet + Val(Left(InchString, SpaceSign - 1)) / 12įracSign = ("/", Word2)įeet = feet + (Val(Left(Word2, FracSign - 1)) / Val(Mid(Word2, FracSign + 1))) / 12 If IsEmpty(FracSign) Or FracSign = 0 Thenįeet = feet + (Val(Left(InchString, FracSign - 1)) / Val(Mid(InchString, FracSign + 1))) / 12 Is it inches or a fraction?įracSign = ("/", InchString) If IsEmpty(SpaceSign) Or SpaceSign = 0 Then InchString = (Left(InchString, InchSign - 1)) If Not IsEmpty(InchSign) Or InchSign = 0 Then ' Strip off the inch sign, if there is one InchString = (Mid(LenString, FootSign + 1)) If Len(LenString) = FootSign Then Exit Function ' Handle the case where the foot sign is the last character If IsEmpty(FootSign) Or FootSign = 0 Thenįeet = Val(Left(LenString, FootSign - 1)) 'Resume Next will prevent VBA from halting execution.įootSign = ("'", LenString) 'The find function returns an error when the target is not found Public Function feet(LenString As String) Type feet as the name of the procedure and indicate it is a function.The custom function LenText will convert a decimal number of feet into a text field showing feet, inches, and fractional inches to the nearest 1/32". The custom function feet() shown below will take a text field in the format that you describe and convert it to decimal feet. Then, how can I convert back to feet and inches in Excel?. How can I use Excel to convert this to decimal feet? I don't want to split the column into two parts. I have an Excel spreadsheet with a column of lengths in the format of 12' 6 7/8".
Update March 2017: Dan Ashby has an improved feet-and-inches-in-excel function available.