CODEWITHSUNDEEP
excelvba
Mark Smith
Mark Smith

Reputation: 69

Enter feet and inches in 0-00 format

I would like to be able to enter feet and inches in a 0-00 format with 1/4 increments.

Examples:
1'3" = 1-03
2'5 1/4" = 2-051
10'9 1/2" = 10-092
22'10 3/4" = 22-103

I have a user form setup that I would like the user to be able to enter dimension as above. However, all my formulas are setup for feet and inches in decimal. So after the user enters the data I would need it to be converted to decimal.

Upvotes: 0

Views: 923

Answers (1)

John Bustos
John Bustos

Reputation: 19574

Parse the input as text...

  1. First 2 digits after the "-" character get divided by 12
  2. Last digit gets divided by 4 then by 12
  3. Add everything together

So, for example, 2-051 = 

2
+ 05/12 = 0.41666
+ (1/4)/12 = .020833
= 2.4375

In VBA, a function for this would look as follows:

Function ParseHeight(ht As String) As Double

Dim Feet As Integer
Dim Inches As Integer
Dim Quarters As Integer

Dim DashLocation As Integer

    DashLocation = InStr(1, ht, "-")

    Feet = Mid(ht, 1, DashLocation - 1)
    Inches = Mid(ht, DashLocation + 1, 2)
    If Len(ht) = DashLocation + 3 Then Quarters = Mid(ht, DashLocation + 3, 1)

    ParseHeight = Feet + (Inches / 12) + ((Quarters / 4) / 12)
End Function

Given that you want to do this via formulas in Excel:

(Assuming input in E1, try this formula in F1):

=LEFT(E1,FIND("-",E1)-1)+MID(E1,FIND("-",E1)+1,2)/12+IFERROR((MID(E1,FIND("-",E1)+3,1)/4)/12,0)

And, then, to convert a number from decimal back to your format, this would do it:

(Assuming decimal to be converted is in F1):

=INT(F1)&"-"&TEXT(INT(MOD(F1,1)*12),"00")&INT(MOD(MOD(F1,1)*12,1)*4)

Upvotes: 0

Related Questions