UDF to convert a value to feet & inches


Well-known Member
Jul 23, 2011
Office Version
365, 2016, 2007
I was able to find several solutions for converting a value to feet and inches where the inches are expressed as an integer plus a fraction. I couldn't find one that expressed the inches as a floating point number (n.nnn..) with the ability to specify the number of decimal points. So I wrote one myself.

The trick is to convert everything to inches, because that's where the rounding is done. Then the feet can be split off and the remaining inches formatted.

Here's the code and them some examples. I'd appreciate any critiques.

'                    Convert a Length (number) to Feet & Inches (string)
'  Syntax: CvtLen2FtIn(pInVal, [pInUnits|"ft"], [pDPOut|2])
'     pInVal    = The number to be converted (real)
'     pInUnits  = The units for pInVal ("ft" (default) or "in")
'     pDPOut    = Number of decimal places in the output (default: 2, range: 0-5))
'     Result    = The converted value as a text field (nn' n.nnn..")
Public Function CvtLen2FtIn(pInVal As Double, _
                   Optional pInUnits As String = "ft", _
                   Optional pDPOut As Integer = 2) As String

Const MyName As String = "CvtLen2FtIn"  'Function name for error messages
Dim TotInch As Double                   'Total length in inches
Dim FeetInt As Double                   'Whole number of feet
Dim Inches As Double                    'Remaining inches, rounded appropriately
Dim Fmt0s As String                     'Format string ("[.]000...")
Dim msg As String                       'Text of error messages

'Convert everything to inches. That's where the rounding will be done.
Select Case UCase(pInUnits)
  Case "IN"
    TotInch = pInVal       'No conversion, it's already in inches
  Case "FT"
    TotInch = pInVal * 12  'Convert from feet to inches
  Case Else
    CvtLen2FtIn = CVErr(xlErrValue)
    Exit Function
End Select

If pDPOut < 0 Or pDPOut > 7 Then  'Check range of pDPOut (0-5)
    CvtLen2FtIn = CVErr(xlErrValue)
    Exit Function
End If

Inches = Round(TotInch, pDPOut)    'Round the inches to the specified number of decimal places
FeetInt = Int(Inches / 12)      'Whole number of feet
Inches = Inches - FeetInt * 12  'Remaining inches rounded appropriately
If pDPOut > 0 Then
  Fmt0s = "0." & String(pDPOut, "0")
  Fmt0s = "0"
End If

CvtLen2FtIn = FeetInt & "' " & Format(Inches, Fmt0s) & """"

End Function
In this table, columns C & D contain the input data. I entered the feet and inches separately so I could compare the results. Column F contains the values in feet (feet + (inches/12). Column G the result of passing that value to the UDF. Columns H & I do the same thing, but with inches. Column J compares the two results to make sure they are identical.

3InputTotalConvertedTotalConvertedG = I?
500000' 0"00' 0"
600200' 0.00"00' 0.00"
700400' 0.0000"00' 0.0000"TRUE
82602.52' 6"302' 6"TRUE
92632.52' 6.000"302' 6.000"TRUE
10111.5501.96252' 0"23.552' 0"TRUE
11111.55511.9629171' 11.6"23.5551' 11.6"TRUE
12111.55531.9629171' 11.555"23.5551' 11.555"TRUE
13111.55541.9629171' 11.5550"23.5551' 11.5550"TRUE
14411.9999904.9999995' 0"59.999995' 0"TRUE
15411.9999924.9999995' 0.00"59.999995' 0.00"TRUE
16411.9999944.9999995' 0.0000"59.999995' 0.0000"TRUE
17411.9999954.9999994' 11.99999"59.999994' 11.99999"TRUE
1890.000109.0000089' 0"108.00019' 0"TRUE
1990.000139.0000089' 0.000"108.00019' 0.000"TRUE
2090.000149.0000089' 0.0001"108.00019' 0.0001"TRUE



Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...