MrExcel.com - Your One Stop for Excel Tips & Solutions

Convert Feet and Inches to Decimal Feet

February 07, 1999 - by

Update March 2017: Dan Ashby has an improved feet-and-inches-in-excel function available.

I have an Excel spreadsheet with a column of lengths in the format of 12' 6 7/8". How can I use Excel to convert this to decimal feet? I don't want to split the column into two parts. Then, how can I convert back to feet and inches in Excel?.

The custom function feet() shown below will take a text field in the format that you describe and convert it to decimal feet.

The custom function LenText will convert a decimal number of feet into a text field showing feet, inches, and fractional inches to the nearest 1/32".

To enter the custom function,

  • Start the VB editor with alt-F11.
  • Insert>Module.
  • Insert>Procedure.
  • Type feet as the name of the procedure and indicate it is a function.
  • Then, copy the following code:
Public Function feet(LenString As String)
    Dim FootSign As Integer
    Dim InchSign As Integer
    Dim SpaceSign As Integer
    Dim FracSign As Integer
    Dim InchString As String
    Dim Word2 As String
    ' Copyright 1999, 2005 MrExcel.com
    LenString = Application.WorksheetFunction.Trim(LenString)
    'The find function returns an error when the target is not found
    'Resume Next will prevent VBA from halting execution.
    On Error Resume Next
    FootSign = Application.WorksheetFunction.Find("'", LenString)
    If IsEmpty(FootSign) Or FootSign = 0 Then
        ' There are no feet in this expression
        feet = 0
        FootSign = 0
    Else
        feet = Val(Left(LenString, FootSign - 1))
    End If

    ' Handle the case where the foot sign is the last character
    If Len(LenString) = FootSign Then Exit Function
    ' Isolate the inch portion of the string
    InchString = Application.WorksheetFunction.Trim(Mid(LenString, FootSign + 1))
    ' Strip off the inch sign, if there is one
    InchSign = Application.WorksheetFunction.Find("""", InchString)
    If Not IsEmpty(InchSign) Or InchSign = 0 Then
        InchString = Application.WorksheetFunction.Trim(Left(InchString, InchSign - 1))
    End If
  
    ' Do we have two words left, or one?
    SpaceSign = Application.WorksheetFunction.Find(" ", InchString)
    If IsEmpty(SpaceSign) Or SpaceSign = 0 Then
        ' There is only one word here.  Is it inches or a fraction?
        FracSign = Application.WorksheetFunction.Find("/", InchString)
        If IsEmpty(FracSign) Or FracSign = 0 Then
            'This word is inches
            feet = feet + Val(InchString) / 12
        Else
            ' This word is fractional inches
            feet = feet + (Val(Left(InchString, FracSign - 1)) / Val(Mid(InchString, FracSign + 1))) / 12
        End If
    Else
        ' There are two words here.  First word is inches
        feet = feet + Val(Left(InchString, SpaceSign - 1)) / 12
        ' Second word is fractional inches
        Word2 = Mid(InchString, SpaceSign + 1)
        FracSign = Application.WorksheetFunction.Find("/", Word2)
        If IsEmpty(FracSign) Or FracSign = 0 Then
            ' Return an error
            feet = "VALUE!"
        Else
            If FracSign = 0 Then
                feet = "VALUE!"
            Else
                feet = feet + (Val(Left(Word2, FracSign - 1)) / Val(Mid(Word2, FracSign + 1))) / 12
            End If
        End If
    End If
End Function

Repeat for the function called LenText. Use this code: 

Public Function LenText(FeetIn As Double)
    ' This function will change a decimal number of feet to the text string
    ' representation of feet, inches, and fractional inches.
    ' It will round the fractional inches to the nearest 1/x where x is the denominator.
    ' Copyright 1999 MrExcel.com
    Denominator = 32 ' must be 2, 4, 8, 16, 32, 64, 128, etc.
    NbrFeet = Fix(FeetIn)
    InchIn = (FeetIn - NbrFeet) * 12
    NbrInches = Fix(InchIn)
    FracIn = (InchIn - NbrInches) * Denominator
    Numerator = Application.WorksheetFunction.Round(FracIn, 0)
    If Numerator = 0 Then
        FracText = ""
    ElseIf InchIn >= (11 + (31.4999999 / 32)) Then
        NbrFeet = NbrFeet + 1
        NbrInches = 0
        FracText = ""
    ElseIf Numerator = Denominator Then
        NbrInches = NbrInches + 1
        FracText = ""    
    Else
        Do
            ' If the numerator is even, divide both numerator and divisor by 2
            If Numerator = Application.WorksheetFunction.Even(Numerator) Then
                Numerator = Numerator / 2
                Denominator = Denominator / 2
            Else
                FracText = " " & Numerator & "/" & Denominator
                Exit Do
            End If
        Loop    
    End If
    LenText = NbrFeet & "' " & NbrInches & FracText & """"
End Function
Convert Feet and Inches to Decimal Feet
Convert Feet and Inches to Decimal Feet

Column A shows the original text. Column B correctly converts to feet any values that contain feet, inches, and / or fractional inches.

Note that if you do not include the foot sign, the value is assumed to be inches. (row 12). If the fractional portion of the inches is not valid, Value! is returned (row 13).

Update: Thanks to Dale Richmond from Kansas City who provided an update in October 2007 to prevent an answer of 1 foot 12 inches when a number greater than 1.999 is passed to the function.