Formulas‎ > ‎Formulas‎ > ‎

Display Feet and Inches as 5' 8 1/2"

posted Jan 30, 2011, 1:05 AM by Ayush Jain   [ updated Mar 26, 2011, 7:06 AM ]

When converting metric distances to English units of feet or inches, you'll end up with a decimal value. But, you may want to display the value as feet and inches using the format 5' 8 1/2".

Let's say you have the decimal value 5.708333 for feet in cell A1. To display feet and inches, use INT(A1) to get the 5 and use A1-INT(A1) to get the remainder (inches). Multiple the remainder by 12 and then use the TEXT function to display the value as "# #/#" inches.

Here's the formula:
=INT(A1)&"' "&TEXT(12*(A1-INT(A1)),"# #/#")&CHAR(34)

Comments