Excel: Formulas for feet and inches

RebeccaR

New Member
Joined
Jan 29, 2019
Messages
4
I am trying to combine 2 cells in excel. I have feet in column Hand inches(fraction form) in column I. I want to combine these two cells in column M and have it display feet and inches in fraction form.

*I do not want to round.

I have tried to use this formula that i found on the web but it does round(Hence the ROUND that didn't register in my head when typing)
=INT(H4)&"'-"&ROUND(((I4-INT(I4))*12),0) & CHAR(34)

I have also tried concatenation but that displays the inches in a decimal.
=CONCATENATE(H4,"-",I4)

I appreciate any help that someone can give. Thank you!
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

GR00007

Board Regular
Joined
Apr 22, 2015
Messages
184
=h4&"-"&text(i4,"#?/?") <-- this will round
=H4&"-"&TEXT(I4,"#??/??") <-- this will also round, but will be more precise, I didn't know how many digits your data goes to - you can try both to see which you prefer
 
Last edited:

BGY23

Well-known Member
Joined
Aug 13, 2008
Messages
515
<colgroup><col width="190" style="width: 143pt; mso-width-source: userset; mso-width-alt: 6948;"> <tbody> </tbody>
=F5&" - "&text(G5,"# ??/??")

Change your cell address to fit. Itdoesn' matter hat your inches cell iindecimal
 

RebeccaR

New Member
Joined
Jan 29, 2019
Messages
4
=h4&"-"&text(i4,"#?/?") <-- this will round
=H4&"-"&TEXT(I4,"#??/??") <-- this will also round, but will be more precise, I didn't know how many digits your data goes to - you can try both to see which you prefer

for example my first set of data is:
H4 = 39
I4 = 7 11/16

I want M4 to say 39' - 7 11/16"


I tried the formula above and it gives me 39-123/16.

Thank you!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,832
Office Version
365
Platform
Windows
Hi & welcome to the board
Try
=H4&"' - "&TEXT(I4,"# ??/??")&CHAR(34)
 

RebeccaR

New Member
Joined
Jan 29, 2019
Messages
4
Hi & welcome to the board
Try
=H4&"' - "&TEXT(I4,"# ??/??")&CHAR(34)

This is perfect. Thank you.

One more question: Is there a way to convert this number to a decimal? I was just told I need both.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,832
Office Version
365
Platform
Windows
How about
=H4+(I4/12)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,832
Office Version
365
Platform
Windows
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,102,630
Messages
5,488,004
Members
407,617
Latest member
Samanthad2007

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top