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!
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,979
Try:

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

Capsaicin Burn

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

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

Capsaicin Burn

New Member
Joined
Jan 26, 2018
Messages
38
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
Joined
Aug 18, 2015
Messages
8,979
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.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
8,979
You're very welcome!

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

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

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top