Share via

Formatting inches and feet in excel

Anonymous
2023-06-26T12:39:41+00:00

I am trying to get a custom format written so that a feet measurement can be 5.5 but display as 5'6" so that i can use it in calculations easier. As close as I have is

#' ?/12'']

which displays 2' 6/12''

is this even possible? it would certainty clean up my books.

Any help is appreciated.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-09-07T03:38:39+00:00

    this didn't work for me. i had 5.333 in a cell and with the custom format [>=1]"'#'0'";[>=0.0833]"'#'0";General the result was '#'0'

    60+ people found this answer helpful.
    0 comments
  2. Anonymous
    2023-11-03T21:18:47+00:00

    Because I was calculating a value in A2 with several decimal places I added a second ROUND function to your formula. Thanks for you help

    =ROUNDDOWN(A2,0)&"'"&ROUND(MOD(A2,1),1)*12&""""

    A2=11.06347903

    B2=11'1.2"

    9 people found this answer helpful.
    0 comments
  3. Anonymous
    2023-06-26T12:54:27+00:00

    Hello Janey,

    You can use the following custom format:

    [>=1]"'#'0'";[>=0.0833]"'#'0";General

    Here's how it works:

    • [>=1] checks if the value is greater than or equal to 1 (feet).
    • If the value is greater than or equal to 1, it displays the whole number part followed by a single quote (#'0').
    • If the value is less than 1, it moves to the next condition.
    • [>=0.0833] checks if the value is greater than or equal to 0.0833 (equivalent to 1 inch).
    • If the value is greater than or equal to 0.0833, it displays the value as a whole number followed by a double quote ('0").
    • If none of the above conditions are met, it uses the General format for other values.

    Using this custom format, you can enter a value like 5.5 and it will be displayed as 5'6" in the cell. The format allows you to perform calculations easily while still displaying the measurement in feet and inches.

    I hope this helps.

    Kindest regards

    Leon Pavesic

    7 people found this answer helpful.
    0 comments
  4. Anonymous
    2023-06-26T13:09:18+00:00

    You may use formula to display what you want. But if you want to calculate it, it is better to use the value in column A.

    =ROUNDDOWN(A2,0)&"'"&MOD(A2,1)*12&""""

    5 people found this answer helpful.
    0 comments
  5. Anonymous
    2023-11-03T21:03:33+00:00

    In this example

    #' ?/12'']

    which displays 2' 6/12''

    I would the display to 2' 6"

    I remember there was a format a few years ago which converted 2.5 [feet] into a display of 2' 6"

    What was that format?

    3 people found this answer helpful.
    0 comments