Following a query at Eng-tips I have had a look at options in Excel for converting metres to feet and inches, with the inches displayed as a fraction rather than a decimal.

Converting from metres to feet or inches is straightforward (with a length in metres in C3):

=CONVERT($C$3,”m”,”ft”) or  =CONVERT($C$3,”m”,”in”)

or just:  =C3/0.3048 or  =C3/0.0254

To display the resulting value as a fraction, the cell may be formatted to display fractions, or the same result may be achieved with the text function, which also allows the string to be terminated with the ‘ or ” symbol:

=TEXT($C$3/0.0254, “# #/16”)&CHAR(34)

The number following the ##/ is the denominator for the fraction, which may be entered explicitly, or read from another cell:

=TEXT($C$3/0.0254, “# #/”&D13)&CHAR(34)

To display the result in feet and inches is not so straightforward.  The simplest method I found came from: http://excel-formulas.blogspot.com.au/2009/07/display-feet-and-inches-as-5-8-12.html

To convert from decimal feet to feet and fractional inches:

=INT($C$5)&”‘ “&TEXT(12*($C$5-INT($C$5)),”#/16″)&CHAR(34)

or for metres to feet and fractional inches:

=INT(CONVERT($C$3,”m”,”ft”))&”‘ “&TEXT(12*(CONVERT($C$3,”m”,”ft”)-INT(CONVERT($C$3,”m”,”ft”))),”# #/16″)&CHAR(34)

Alternatively, a short User Defined Function (UDF) will give the same result with:

=M2Ftinf($C$3,16)

These formulas, and 4 UDFs (converting to inches or feet and inches, and in the opposite direction) are included in the download file: M2ft-in.xlsb:

Metres to feet and fractional inches

On-sheet formulas for conversion in the other direction would be still more complex, but the UDFs provide this functionality.

Feet and inches to metres

The four UDFs have also been added to the Units4Excel spreadsheet, which also provides functions for conversion between a wide range of other units, and units aware evaluation of functions entered as text.  The spreadsheets (including full open-source code) may be downloaded from Units4Excel.zip