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