+ Reply to Thread
Results 1 to 4 of 4

display feet and inches

  1. #1
    jkrager
    Guest

    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. #2
    Daniel CHEN
    Guest

    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" <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3
    bpeltzer
    Guest

    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. #4
    Registered User
    Join Date
    10-19-2012
    Location
    pa
    MS-Off Ver
    excel 2010
    Posts
    2

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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