Feet and Inches in Excel

Post date: Dec 28, 2015 10:15:14 PM

If you are in the American construction industry, you know the frustrations dealing with feet and inches in Excel. Here are some easy tips for overcoming this Excel shortcoming.

Keep Data Entry Simple:

I often see XL users enter measurements as 5 ft. 4 3/4 In. I recommend the construction industry standard because it is easier to enter: 5' 4 3/4".

Work in Inches

We may enter measures in feet and inches, but we can greatly simplify our calculations by converting all measures to inches and working only in inches. Here is a short UDF to convert data entered in the construction industry standard format to inches only:

Public Function ToInches(ByVal sEntry As String) As Currency

Dim sFormula As String

sFormula = Replace(sEntry, "'", " * 12 " & _

IIf(InStr(1, sEntry, """"), " + ", ""))

sFormula = Replace(sFormula, """", "")

ToInches = Evaluate(sFormula)

End Function

Display Fractions

Accountants use decimals. The construction industry uses fractions. Excel accommodates fractions amazingly well. To format 5.75 inches as a fraction go to the HOME tab in Excel's ribbon. Click the down arrow in the Number group. When the Format Cells dialog appears, click Fraction in the Category listbox and Up to two digits (21/25) in the Type listbox. 5.75 inches will now display as 5 3/4.

Present Results in Feet and Inches

The above number formatting only works with inches. While sufficient for calculations, final results should in the construction industry standard. To display inches in the construction industry standard use this formula:

=INT(A1/12)&"' "&TRIM(TEXT(MOD(A1,12),"# ??/??")) & """"

The above formula assumes the entry we want displayed in construction industry standard format is in cell A1.