Sometimes, converting a measurement in inches to feet and inches (i.e. 63 to 5′ 3″) becomes essential. Because we are accustomed to getting some terms in feet and inches format (e.g. height, length, etc.) rather than just in inches or feet. So, this tutorial will help you to know how to convert inches to feet and inches in Excel.
How to Convert Inches to Feet and Inches in Excel: 5 Methods
As we all know, one foot equals 12 inches. As a result, dividing the measurement in inches by 12 yields the consequent value in feet. However, we want to show the resultant figure in both feet and inches.
We’ve got a dataset of 10 individual and their consecutive Heights in inches. We want to get it in feet and inches.
So, here we are providing 5 useful methods to convert inches to feet and inches format.
1. Applying INT and MOD Functions
You can use a formula based on the INT and MOD functions to convert inches to feet and inches in Excel. Follow the steps carefully.
Steps:
- First, select cell D5, type down the formula below, and press ENTER to get the result in feet and inches.
=INT(C5/12)&"' "&MOD(C5,12)&""""
Formula Breakdown:
The formula converts the numeric value of inches to text that represents the same amount in inches and feet.
Firstly, we use the INT function to get the value for feet. The formula for this part is as below:
=INT(C5/12)&"' "
In the INT function, inside the parentheses, the value in cell C5 is divided by 12 as 1 foot is equal to 12 inches. INT function simply rounds down to its nearest integer value. Actually, it returns the integer part of the number, removing any decimal portion.
A single quote is put inside a quotation mark to mean feet sign, with an ampersand (&) operator which is concatenated with the INT function.
Next part of the formula is as below:
=MOD(C5,12)&""""
The MOD function returns the remainder after division. Here the value in cell C5 is the dividend and 12 is the divisor.
This result is concatenated with two pairs of double quotes. The outer pair is for indicating text and the inner pair is to indicate the inch symbol.
Finally, both the functions are concatenated with the ampersand operator
- Then, get the cursor at the right bottom part of cell D5 and use the Fill Handle tool to copy the formula in other cells with a view to getting the values of these cells in feet and inches.
Read More: How to Convert Inches to Feet in Excel
2. Converting Inches to Feet and Inches with Labels
In our previous method, we showed our output in mathematical form (e.g. 5’ 8”). In this method, we will show you how to display your result with labels (e.g. 5ft. 8in.). Follow the steps below:
- First, select cell D5, write down the formula below, and then press ENTER.
=INT(C5/12)&"ft. "&MOD(C5,12)&"in."
Here, we can notice that with our previous formula, the only difference is that we interchanged the “single quote” and “double quote” with “ft.” and “in.”. So, in return, it displays ft. and in. instead of a quote sign.
Read More: How to Convert Decimal Feet to Feet and Inches in Excel
3. Utilizing the ROUND Function to Convert Inches
In our last 2 methods, we may observe that in the 4th row, the Height of Susan is 5ft. 0.539999999999999in. which is pretty odd and unpresentable. To avoid this problem, we’ll use the ROUND function with our previous formula. Pay attention to the steps below.
Steps:
- Select cell D5, put down the formula below, and press ENTER. Use the Fill Handle tool and drag it down to get the whole results.
=INT(C5/12)&"' "&ROUND(MOD(C5,12),2)&""""
Here, the MOD function returns the remainder after division and the ROUND function returns the decimal value to 2 digits. So now these heights in feet and inches look pretty much presentable.
Read More: How to Convert Inches to Square Feet in Excel
4. Converting Inches to Feet and Inches If the Input Is Negative
If the heights of individuals are given as negative numbers, then INT and MOD functions cannot be performed. To solve this complication, we’ll use TRUNC and ABS functions in this method. Notice the following steps:
- Choose cell D5 and type down the formula as follows, then press ENTER.
=TRUNC(C5/12)&"' "&ROUND(MOD(ABS(C5),12),2)&""""
Here, we use the ABS function to ensure that the integer inside MOD is positive and as well as replace INT with the TRUNC function. The INT function rounds negative numbers down away from zero. TRUNC function, on the other hand, simply removes the decimal value and leaves the integer along with the sign. The MOD function also doesn’t work correctly with negative numbers. So, using the ABS function, we will get the absolute value always to work with the MOD function. Now, this formula can completely run with both positive and negative numbers.
Read More: How to Convert Inches to Cm in Excel
5. Using CONVERT Function to Transform Inches
Lastly, we also can apply the widely used the CONVERT function to convert inches to feet and inches in a unique way. But we cannot do it in one step. Because the CONVERT function transforms inches to feet or feet to inches directly, not inches to feet and inches. So, we have to follow some additional procedures after this function.
Steps:
- Select cell D5 and write down the formula below and then press ENTER.
=ROUND(CONVERT(C5,"in","ft"),3)
Here, we’ve taken the value from cell C5 as a number and converted it from inches to feet. Then, using the ROUND function, round up the decimal value to 3 digits to look clean.
- Afterward, select cell E5 and paste the below formula, and press ENTER.
=TRUNC(D5)&"' "&ROUND((D5-TRUNC(D5))*12,2)&""""
For getting the feet part we used the TRUNC function. To get the inches part, we must multiply the decimal portion of cell D5 with 12. For this, we subtracted the integer part from the whole number of cell D5 and multiplied it by 12. At last round down the decimal number to 2 digits to give it a cleaner look. And the feet and inch signs are put as our previous methods.
Read More: How to Convert Feet to Inches in Excel
Things to Remember
- In the result, we got our measurements in feet and inches in text format. So, we cannot use these cell references as values in other formulas.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
Conclusion
Here we’ve tried to show you 5 methods of converting inches to feet and inches in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section.
Related Articles
- How to Convert inch to mm in Excel
- How to Convert Units in Excel
- How to Convert Inches to Meters in Excel
- How to Convert Feet and Inches to Decimal 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!
Nice writing. Actually my ques is out of this topic. But I’m facing this problem severely in my spreadsheet. In some cells of my sheet, there are some extra spaces, but i cannot figure out how to remove them automatically. This creates error in calculations also. Could you help on this?
Hello JACOB,
Thanks for your appreciation first. Now coming back to your question. I understood your problem easily. It takes a great effort if you try to remove those extra spaces manually from the cells one by one. But no problem. We have a smart workaround for you. Just follow the following steps.
We can see that there are some spaces in cells in the C5:C8 range which we want to remove.
• Firstly, press CTRL+F on your keyboard.
• Immediately, the Find and Replace dialog box appears
• Secondly, go to the Replace tab.
• In the Find what box, type down a single blank space.
• Keep the Replace with box blank.
• After that, click on the Replace All button.
Instantly, it’ll show a message box with the message “All done. We made 20 replacements.”. Also, the extra spaces are gone in the dataset.
Additionally, you can go through the article How to Remove Blank Spaces in Excel in our website to know more techniques regarding this problem. Also, follow our website, ExcelDemy, a one stop Excel solution provider to explore more. Happy Excelling.