Is it possible to convert a decimal number to feet and inches in a single cell?

[Capsaicin Burn]

Is it possible to convert a decimal number to feet and inches in a single cell? I have a formula that outputs feet and inches as a decimal number and I would like to convert it to show feet and inches with the appropriate symbols.

Example:
37.74 converted to 37' 8 5/16"

To add another layer of difficulty, the inches needs to be in a tape measure friendly format. I was told how to format a cell for inches only but I would like to do the feet and inches in one cell if possible.

Can this be done at all and if so can it be done with a formula without using VB?

Thanks for any help!

 

[Eric W]

Try:

=INT(A1)&"' "&INT(MOD(A1,1)*12)&""""&TEXT(MOD(A1*12,1)," ??/16")

 

[Capsaicin Burn]

Additional Information:

Here is what I have been told but I'm still having a slight problem.

Example:
Cell B2: =37.74
Cell C2: =ROUNDDOWN(B2,0)
Cell D2: =MOD(B2,1)*12
Cell E2: =C2&"' "&TEXT(D2,"0 #/##")&CHAR(34)

D1 then displays 37' 8 64/77" which is what I want. However, formating D1 to a fraction does not help me with the 65/77" being a useful fraction. Simplified 65/77 = 5/6 which is more useful but how do I convert that? Is it a formula mod in E2?

 

[Capsaicin Burn]

Wow, this worked perfectly!

Hours of internet searching said it couldn't be done in a single cell. I need to improve my searching technique.

Thank you so much Eric W!!!

 

[Capsaicin Burn]

I noticed that the " character is after the 8 instead of after the 13/16. Can that be moved to the end?

(with 37.74 be the decimal number)

 

[Eric W]

Sure, try this:

=INT(A1)&"' "&INT(MOD(A1,1)*12)&TEXT(MOD(A1*12,1)," ??/16")&""""

Keep in mind that this is a text value, and you can't use it in calculations. Use your original value if you have to calculate with it. But it should be just fine for a display value.

 

[Capsaicin Burn]

Eric your knowledge and help to me is/was simply outstanding and I thank you very much!


-Jim

 

[Eric W]

You're very welcome!

Here's a slightly shorter version of the last formula:

=INT(A1)&"' "&TEXT(MOD(A1,1)*12,"? ??/16")&""""