# display feet and inches

1. ## display feet and inches

Is there any way in excel to display feet and inches? I can get decimal feet
and even fractional feet ie. 3 1/3. Is there any way to multiply the
fraction portion of that by 12 to get it to spit out 4? Even if it ends up
in two differnet cells, I can get another cell to read 3 and the next cell to
read 4, and squash them together to look like 3'-4".

2. ## Re: display feet and inches

Use the following formula:
=INT(A1)&"'-"&ROUND(((A1-INT(A1))*12),0) & CHAR(34)

Suppose you have 15.92 in cell A1, you will have 15'-11" from the above
formula. The formula rounds to the nearest inch.

The following one is much more complex, but it converts the decimal feet to
ft-in and fractional inch (64 based)
12.12345 ft will be converted to 12'-1 31/64".

=INT(A1)&"'"&"-"&INT(12*(A1-INT(A1)))&IF((A1-INT(A1))*12-INT((A1-INT(A1))*12)>1/128,"
","")&IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*64+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*64+0.5)&"/64",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*32+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*32+0.5)&"/32",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*16+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*16+0.5)&"/16","")))
&IF(OR(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*64+0.5),ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*32+0.5),ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*16+0.5)),"",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*8+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*8+0.5)&"/8",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*4+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*4+0.5)&"/4",IF(ISODD(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*2+0.5),INT(((A1-INT(A1))*12-INT((A1-INT(A1))*12))*2+0.5)&"/2",""))))&CHAR(34)

You can download a utility program from
http://www.xldatasoft.com/downloads/Conversion.xls

--
Best regards,
---
Yongjun CHEN
=================================
XLDataSoft - Data Analysis Expert, Excel/VBA Specialist
- - - - www.XLDataSoft.com - - - -
Free Excel-Based Data Processing Tool is Available for Download
Free Excel / VBA Training Materials is Available for Downloand
=================================

"jkrager" <jkrager@discussions.microsoft.com> wrote in message
news:533B0F34-BA1F-4569-8F69-470A44395969@microsoft.com...
> Is there any way in excel to display feet and inches? I can get decimal
> feet
> and even fractional feet ie. 3 1/3. Is there any way to multiply the
> fraction portion of that by 12 to get it to spit out 4? Even if it ends
> up
> in two differnet cells, I can get another cell to read 3 and the next cell
> to
> read 4, and squash them together to look like 3'-4".

3. ## RE: display feet and inches

If the number of feet (3 1/3) is in a1, then =INT(A1) & "'-" & 12*MOD(A1,1) &
""""
will display as you described at the end.
--Bruce

"jkrager" wrote:

> Is there any way in excel to display feet and inches? I can get decimal feet
> and even fractional feet ie. 3 1/3. Is there any way to multiply the
> fraction portion of that by 12 to get it to spit out 4? Even if it ends up
> in two differnet cells, I can get another cell to read 3 and the next cell to
> read 4, and squash them together to look like 3'-4".

4. ## Re: display feet and inches

If you want to just display an architectual/engineering type feet and inches, such as 1'-6 1/2" try this. It converts 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.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1