Meters to Feet for Excel     |     Home



What is it?

This is a custom function written in Visual Basic for Applications (VBA) that you can add to an Excel workbook. The function converts meters to feet and fractional inches with a user-specified level of fractional-inch accuracy.

This is useful when the desired output of a calculation is feet and fractional inches, as opposed to decimal inches. One example is the display of coax cable lengths since few tape measures provide decimal feet and none that I've seen provide decimal inch measurements.

Example File Download

The example Excel file linked below contains basic instructions on how to use this custom function and how to to add it to your own Excel files.

Example: placing the formula =m2ft(2.49,5) into a worksheet cell produces the result 8' 2-1/32"

m2ft.xls

The Code

For those who just want the code, here it is. Works great for Excel 2003, not sure about other versions.

Function m2ft(meters As Variant, level As Variant) As Variant

'function converts meters to feet and fractional inches with
'1/(2^'level') accuracy; e.g., for 1/16th-inch accuracy, set level = 4.
'
'Usage --> m2ft(meters, level)
'where
'meters = measurement in meters to be converted to feet & inches
'level = granularity of fractional inches to the nearest 1/(2^level) of an inch
'
'E.g., m2ft(2.49,5) returns the string <8' 2-1/32"> (without the brackets)
'
'Dave Typinski, 2006

If meters = 0 Then

m2ft = "0" & Chr$(34)

Else

'convert meters to decmal inches
inches = meters / 0.0254

'find number of fractions per inch and per foot
fracperinch = 2 ^ level
fracperfoot = fracperinch * 12

'find number of fracs in measurement
fracs = Round(inches * fracperinch, 0)

'find number of whole feet in measurement
wholefeet = Int(fracs / fracperfoot)

'find leftover fracs less than 1 foot
fracsleft = fracs - wholefeet * fracperfoot

'find whole inches
wholeinches = Int(fracsleft / fracperinch)

'find leftover fracs less than 1 inch
fracsleft = fracs - wholefeet * fracperfoot - wholeinches * fracperinch

'make proper fraction -- e.g., convert 4/16" to 1/4"
While fracperinch > 2 And fracsleft Mod 2 = 0
fracsleft = fracsleft / 2
fracperinch = fracperinch / 2
Wend

'build output string
If wholefeet = 0 Then
'no whole feet, build inches only
If wholeinches = 0 Then
'no whole inches, build inch fraction only & add double hashmark
out = CStr(fracsleft) & "/" & CStr(fracperinch) & Chr$(34)
Else
'build whole inches, with fractional inches if needed
out = CStr(wholeinches)
If fracsleft = 0 Then
'no fractional inches, build only whole inches & add double hashmark
out = out & Chr$(34)
Else
'farctional inches exist, build whole inches and inch fraction & add double hashmark
out = out & "-" & CStr(fracsleft) & "/" & CStr(fracperinch) & Chr$(34)
End If
End If
Else
'build whole feet, add single hashmark, add inches
out = CStr(wholefeet) & "' " & CStr(wholeinches)
If fracsleft = 0 Then
'no fractional inches, finish whole inches with double hashmark
out = out & Chr$(34)
Else
'fractional inches exist, add fraction and finish with double hashmark
out = out & "-" & CStr(fracsleft) & "/" & CStr(fracperinch) & Chr$(34)
End If
End If

'return the formatted output string
m2ft = out

End If

End Function


Dave Typinski, 2014