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



Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...