The dataset contains students’ names and their height. To convert cm to feet and inches:
Method 1 – Using the CONVERT Function to Convert Cm to Feet and Inches
1.1 Cm to Feet
Steps:
- Go to D5 and enter the following formula:
=CONVERT(C5,"cm","ft")
- Excel will display the list of units. Choose Feet or enter it manually.
- Press ENTER to see the result.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: How to Convert MM to CM in Excel
1.2 CM to Inches
Steps:
- Go to D5 and enter the following formula:
=CONVERT(C5,"cm","in")
- Press ENTER to see the result.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: Converting CM to Inches in Excel
Method 2 – Convert Cm to Feet and Inches
Steps:
- Go to D5 and enter the following formula:
=TRUNC(C5/2.54/12)&"' "&ROUND(MOD(C5/2.54,12),0)&""""
Formula Breakdown:
MOD(C5/2.54,12) ⟶ Returns the remainder after dividing (C5/2.54) by 12.
Output ⟶ 10.07874
ROUND(MOD(C5/2.54,12),0) ⟶ Rounds the number to a specified digit.
ROUND(10.07874,0)
Output ⟶ 10
TRUNC(C5/2.54/12) ⟶ Truncates a number to an integer.
Output ⟶ 5
TRUNC(C5/2.54/12)&”‘ “&ROUND(MOD(C5/2.54,12),0)&”””” ⟶ Returns the final output.
5&”‘ “&10&””””
Output ⟶ 5’10”
- Press ENTER.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: How to Convert Square Feet to Square Meters in Excel
Method 3 – Convert Cm to Feet and Fractions of Inches
Steps:
- Go to D5 and enter the following formula:
=INT(CONVERT(C5,"cm","ft")) & "' " & TEXT(12*(CONVERT(C5,"cm","ft")-INT(CONVERT(C5,"cm","ft"))),"0.00") & """"
Formula Breakdown:
INT(CONVERT(C5,”cm”,”ft”)) ⟶ Rounds the number to the nearest integer.
Output ⟶ 5
12*(CONVERT(C5,”cm”,”ft”)-INT(CONVERT(C5,”cm”,”ft”))) ⟶ Returns the output after conversion and calculation.
Output ⟶ 10.0787401574803
TEXT(12*(CONVERT(C5,”cm”,”ft”)-INT(CONVERT(C5,”cm”,”ft”))),”0.00″) ⟶ Converts the number into text with 0.00 format.
Output ⟶ “10.08”
INT(CONVERT(C5,”cm”,”ft”)) & “‘ ” & TEXT(12*(CONVERT(C5,”cm”,”ft”)-INT(CONVERT(C5,”cm”,”ft”))),”0.00″) & “”””⟶ Returns the final output.
5&”‘ “&10.08&””””
Output ⟶ 5’10.08”
- Press ENTER to see the result.
- Drag down the Fill Handle to see the result in the rest of the cells.
Read More: Millimeter(mm) to Square Meter Formula in Excel
Things to Remember
- 1 inch = 2.54 cm
- 1 feet = 12 inches
Download Practice Workbook
Related Articles
- How to Convert Meters to Feet in Excel
- How to Convert Feet to Meters in Excel
- Convert Cubic Feet to Cubic Meters in Excel
- How to Convert Lbs to Kg in Excel
- How to Convert Kg to Lbs in Excel
- How to Convert Radians to Degrees in Excel
<< Go Back to Excel CONVERT Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
