Method 1 – Using the INT & MOD Functions
Steps:
- Select the D5 cell.
- Enter this formula in the D5 cell to directly convert decimal feet to feet-inches. You can copy and paste this formula from here.
=INT(C5)+(12*MOD(C5,1)>=11.5)&"'"&IF(12*MOD(C5,1)>=11.5,0,ROUND(12*MOD(C5,1),0))&""""
The C5 cell refers to the Height in decimal feet. Using the INT function in conjunction with the MOD function is a popular way to convert decimal feet to feet-inches.
- Dsplay the results by pressing ENTER.
- Use the Fill Handle tool in order to complete the conversion of Height in decimal feet to feet-inches.
Method 2 – Using ROUNDDOWN Function to Convert Decimal Feet to Feet and Inches in Excel
Step 1: Obtain the Feet from the Height
- Begin, choose a cell, for this example. We chose the D5 cell.
- Enter the ROUNDDOWN function and provide the 2 necessary arguments. The C5 cell refers to the Height in feet, while the 0 tells the ROUNDDOWN function to display only the integer value.
Steps 02: Extract Inch from the Height
- Choose the E5 cell and enter the following expression, which you can copy from here.
=ROUND((C5-D5)*12,0)
- Click ENTER to get the results.
Steps 03: Combine Feet & Inch
- Repeat the same process for the F5 cell while typing in the following formula.
=CONCATENATE(D5,"ft"," ",E5,"in")
- This links the feet and the inch into a single column.
- Use the Fill Handle to drag down and fill up the table.
- The Height in decimal feet gets converted to feet-inches.
Method 3 – Using INT & TEXT Functions
Steps:
- Select a target cell, we hosen the D5 cell.
- Copy and paste this formula and enter it into the D5 cell.
=INT(C5) & " ft " & TEXT(MOD(C5,1)*12, "# ??/16") & "in"
The C5 cell represents the Height in feet and the TEXT function enables you to format the number.
- Press ENTER to show the results and utilize the Fill Handle tool to fill out the rows.
Method 4 – Using IF, ROUNDDOWN, and MOD Functions
Steps:
- Navigate to the D5 cell and insert the expression given below.
=IF(NOT(ISNUMBER(C5)),”n/a”,IF(OR(C5>=1,C5<=-1),ROUNDDOWN(C5,0)&"'-"&TEXT(MROUND(MOD(ABS(C5*12),12),1/16),"0 ##/###")&"""",TEXT(MROUND(ABS(C5*12),1/16)*SIGN(C5),"# ##/###")&""""))
The C5 cell represents the Height in Feet.
- Use the Fill Handle to copy the formula into the cells below.
Download Practice Workbook
Related Articles
- How to Convert inch to mm in Excel
- How to Convert Units in Excel
- How to Convert Inches to Cm in Excel
- How to Convert Inches to Meters in Excel
- How to Convert Millimeters (mm) to Inches (in) in Excel
- How to Convert Millimeters (mm) to Feet (ft) and Inches (in) in Excel
<< Go Back to Excel CONVERT Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
How would one modify the last option using INT & TEXT to display the fraction in it’s lowest common denominator? Such as 12/16 displays as 3/4?
Hi Kate,
Use MROUND() function, round the number to desired multiple such as 1/2, 1/8, 1/16, 1/32…
with combination TEXT() format function you will get the lowest common denominator.
The text format would look like these:
“# ##/###”
or
“0 ##/###”
Phong
Hello KATE,
Thank you for your question. You can reduce a fraction to its lowest term by specifying the format style of the TEXT function to: =INT(C5) & ” ft ” &TEXT(MOD(C5,1)*12, “000/00”.) & “in”
In general, INT() function is OK for person height conversion as such, positive number.
But in case of measurement, if involve with negative number it become problematic!.
Use ROUNDDOWN() function as you can, down the road it will save you lot of headach.
The formula below will cover whole range of numbers, from negative to positive and round-off to 1/16″
=IF(NOT(ISNUMBER(A1)),”n/a”,IF(OR(A1>=1,A1<=-1),
ROUNDDOWN(A1,0)&"'-"&TEXT(MROUND(MOD(ABS(A1*12),12),1/16),"0 ##/###")&"""",
TEXT(MROUND(ABS(A1*12),1/16)*SIGN(A1),"# ##/###")&""""))
Phong
Hello Phong,
Thank you for your suggestion and for taking the trouble to provide a great solution to Kate’s problem. We, the ExcelDemy team really appreciate your effort and as a result, we’ve updated our article to include the solution that you’ve provided.
Hello Eshrak Kader,
We all learner, I ran to problem before and someone pointed me to right direction, I just hope my little tips could help!
Cheer!
BTW, if you are inerested in VBA, regarding convert to imperal unit in Excel, let me know, I could point you some of my posts.
Phong