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

#### Capsaicin Burn

##### New Member
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

##### MrExcel MVP
Try:

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

#### Capsaicin Burn

##### New Member

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

##### New Member
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

##### New Member
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:

#### Eric W

##### MrExcel MVP
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

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

-Jim

#### Eric W

##### MrExcel MVP
You're very welcome!

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

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

1,082,043
Messages
5,362,825
Members
400,694
Latest member
Sofie17

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...