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

Capsaicin Burn

New Member
Joined
Jan 26, 2018
Messages
38
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!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?
 
Upvote 0
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!!! :biggrin:
 
Upvote 0
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)
 
Last edited:
Upvote 0
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.
 
Upvote 0
You're very welcome!

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

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

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top