Thursday, 18 June 2015

Microsoft Excel: Convert Centimeters and Meters to Feet and Inches

Microsoft Excel does not have a built-in function to convert any measurement of length into inches and feet with single and double quote. Therefore, I have following formula that can convert any length specified in centimeters into feet and inches of x' x" format:

=TRUNC(B3/2.54/12)&"' "&TRUNC(MOD(B3/2.54,12))&""""

B3 is the address of cell containing centimeters.

Practical example:

Similarly, to convert meters into feet and inches of x' x" format use following formula:

=TRUNC(B8*100/2.54/12)&"' "&TRUNC(MOD(B8*100/2.54,12))&""""

B8 is the address of cell containing meters.

Practical example:

Warning: There are two disadvantages of above two formulas. First, results (6' 1" & 5' 3") are in text format rather than numerical format, therefore, they cannot be used in further calculations. Second, for simplicity sake, fractions of inches are simply dropped, not rounded off, but dropped.

Following formulas will round off inches to the nearest integer:

cm to ft' in"

=TRUNC(B3/2.54/12)&"' "&ROUND(MOD(B3/2.54,12),0)&""""

m to ft' in"

=TRUNC(B8*100/2.54/12)&"' "&ROUND(MOD(B8*100/2.54,12),0)&""""

No comments: