# Convert Feet and Inches to Decimal Feet

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
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.
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