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

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). Multiply 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)

13 comments:

Anonymous said...

Thanks, good info. How would you calculate the difference between 70'0" from 45'3"? I have excel displaying the feet inches with custom mask (###'##\"). So if I type in the cell 4503, the display shows 45' 03". Unfortunatly it does the math that way 7000-4503=2497 (24'97"). Thanks.
Barry

Anonymous said...

Thank you.

...there needs to be more @ Functions for us regular Joes. or at least as an Add-In.

Anonymous said...

This is a good try. only one thing: When I typed in the number20.7 the output returned 20' 8 2/5"
There isnt a 2/5 on the feet/inches scale. any work around there for that?

Anonymous said...

Alternatively, you could use custom format
#'" "#/12
which will display 5.3 as 5' 4/12 which is 5 feet 4 inches -- not perfect, but useful.

ModernThinker said...

If you want to use fractions of an inch in standard format i.e. 1/4", 1/8", 1/16" etc. change the formula to:
=INT(A1)&"' "&TEXT(12*(A1-INT(A1)),"# #/4")&CHAR(34) OR
=INT(A1)&"' "&TEXT(12*(A1-INT(A1)),"# #/8")&CHAR(34) etc. to get the desired fraction conversion

RADIOMAN said...

Want to build antennas. The calculator gives results like "3ft5-15/32in" or "1ft3-5/8in". How can I proceed to have my results in meters ? My TI-30X2B (and I)turns warm !Is it an answer with Excel ? Many thanks for your help ! M.L.

Anonymous said...

If the value of A1 is in inches then this will convert the display to feet and inches rounding to the nearest 1/8":

=MROUND(A1,1/8)/12 & "' " & TEXT(MOD(MROUND(A1,1/8),12),"#-#/##") & """"

you can change the 1/8 to 1/16 for 16ths of an inch rounding.

Anonymous said...

This converts inches and decimal inches to feet, inches and fractions:

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

It rounds to 1/8 inche; but you can change the 1/8 to 1/16 if you like.

Anonymous said...

Also can use the following to convert decimal inches to feet and inches:

Make a column with the dimension in decimal inches (ie, 18.5). Keep the dimension correct as rounded to something reasonable like 1/16" (0.625 NOT 0.63).

Next to it use this formula:

=CONCATENATE((E13-MOD(E13,12))/12,"'-",ROUNDDOWN(MOD(E13,12),0),IF(MOD(E13,1),CONCATENATE(" ",TEXT(MOD(E13,1),"##/##"),""""),""""))

Replace the E13's in the formula with the cell ID with the decimal inches. Voila!

We figured this out when we were trying to format Design Tables for insertion into drawing sheets in Solidworks CAD system.

Anonymous said...

Use =INT(A1/12)&"' "&INT(12*(A1/12-INT(A1/12))) & "-" & TEXT(INT(MOD(12*(A1/12-INT(A1/12)),1)*16),"#")&"/16"&CHAR(34) to take decimal inches and convert to the nearest feet inches and 16th of inch. Such as 657.643 inches will result in

54' 9-10/16"

Assumes A1 is cell with decimal inches.

Anonymous said...

While all of these formulas are mathematically correct, there is a huge "0" missing from these displays. 9'- 1/2" is confusing to the point where it is not productive. Solve for the missing "0" when the length involves feet and inches and the inches is under 1.

Anonymous said...

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

This adds the zero from the 1:09pm post

Anonymous said...

Fantastic!