CODEWITHSUNDEEP
excelexcel-formulaconvertersvba
MJ95
MJ95

Reputation: 479

Convert feet and inches to decimal format

I have approximately 3600 heights with the format Feet-Inch-Inch-Eigth of an inch, so for example:

5103 = 5 feet, 10 and 3/8 inches
6000 = 6 feet
6022 = 6 feet, 2 and 2/8 inches

and I need to convert these to decimal format either with a formula or macro, so that the results for these would be:

5103 = 5.864
6000 = 6.000
6022 = 6.187

As I have 3600+ of these, manual work would be a pain. How can I convert this format to a decimal format of feet and inches?

Upvotes: 0

Views: 4996

Answers (2)

Gary's Student
Gary's Student

Reputation: 96773

With data in column A, in B1 enter:

=--LEFT(A1,1)+MID(A1,2,2)/12+RIGHT(A1,1)/(12*8)

and copy down:

enter image description here

NOTE:

this conversion yields decimal feet so that 2 feet 6 inches shows 2.5

EDIT#1:

If you need to accommodate more than 9 feet in column A, then the formula should be:

=--LEFT(A1,LEN(A1)-3)+MID(A1,LEN(A1)-2,2)/12+RIGHT(A1,1)/(12*8)

Upvotes: 3

user4039065
user4039065

Reputation:

If you ever need to go the long route, a VBA UDF would seem to be the most expedient.

Function feet2dec(str As String) As Double
    Dim v As Long, vtmp As Variant, tmp As String
    Dim feet As Double

    vtmp = Split(str, Chr(44))
    For v = LBound(vtmp) To UBound(vtmp)
        Select Case LCase(Right(Trim(vtmp(v)), 4))
            Case "feet"
                feet = feet + Val(Trim(Replace(vtmp(v), "feet", vbNullString, 1, -1, vbTextCompare)))
            Case "ches"
                str = Replace(vtmp(v), "inches", vbNullString, 1, -1, vbTextCompare)
                str = Application.Trim(Replace(str, "and", vbNullString, 1, -1, vbTextCompare))
                feet = feet + Application.Evaluate(str) / 12
        End Select
    Next v

    feet2dec = feet
End Function

   feet2dec

Upvotes: 1

Related Questions