Feet Inches to Decimal Values

I have a cost estimate sheet that will require estimators to enter in a measurement that will need to multiplied to figure out the square foot value.  I would like something in VBA that would target 2 particular cells so that if the estimators want they can make the entry in a particular cell and it would convert it from any of 3 formats.  The answer should always be expressed in footage which could be the multiplied to square footage (see spreadsheet).

4' 5-1/2" =4.458 (ft)
30" = 2.5 (ft)
30 3/4" = 2.5625 (ft)

Not all of the estimators have this issue of not being able to convert this in their head so i set up a sheet that will allow them to use this if they need to or not.  I tried to be very specific on my sheet but feel free to ask any questions you have.
Converter-square-footage.xlsm
farrissfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Their you go..have a look Here

This gives you step by step tips about what you are trying to do..

Saurabh...
farrissfAuthor Commented:
Thank you for your submission, I would really like the solution in VBA.  This eliminates the possibility of someone overwriting a formula and I eventually want to move the macro to a user box that will pop up.
Saurabh Singh TeotiaCommented:
When you say vba..what you are looking for?? Running a macro and getting a value or what exactly you are looking for??

Saurabh...
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

farrissfAuthor Commented:
I am looking for a macro.  Did you look at the sheet I sent a comprehensive set of requirements?  Take a look and see if that makes sense.
Roy CoxGroup Finance ManagerCommented:
Why would you want a macro, a formula would be the most efficient way. See the first block in the example where I have added a formula to produce the square footage
Converter-square-footage.xlsm
DougCommented:
I would suggest avoiding a macro for such a simple formula (multiplication). If your users are on their own machines and are unfamiliar with macros and security settings then it will cause more headache than it's worth and I speak from experience. You could do a few things to make the user interface more friendly and you could apply worksheet protection to prevent changes to the formula cell.

If you still prefer a macro then you might as well get the user form set up now.

Do you still want the macro or would it be acceptable without the vba?
farrissfAuthor Commented:
Thank you for the comments, I have some other reasons for using macro, I like the fact that the macro can deal with syntax that I described in the requirements including the fractional measurements, because that is what the folks in the group wanted (and my boss).  As far as the security, it is not a concern as they already use several macros.  I haven't designed the user form because I am not sure how and there s some other features that need to be added to it in addition to this "converter tool".
Saqib Husain, SyedEngineerCommented:
Try this macro in the "Worksheet module"

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Offset(-1) = "Convert" Then
    If Target.Offset(, 3) = "X" Then
        Target.Offset(, 1) = ft2dec(Target)
    Else
        Target.Offset(, -1) = ft2dec(Target)
    End If
End If
End Sub

Function ft2dec(x)
st = x
If InStr(st, "'") > 0 Then
    ft = Left(st, InStr(st, "'") - 1)
    st = Right(st, Len(st) - InStr(st, "'"))
End If
If InStr(st, """") > 0 Then
    If InStr(st, "-") > 0 Then
        inc = Left(st, InStr(st, "-") - 1)
        st = Right(st, Len(st) - InStr(st, "-"))
        numr = Left(st, InStr(st, "/") - 1)
        st = Right(st, Len(st) - InStr(st, "/"))
        denom = Left(st, InStr(st, """") - 1)
    ElseIf InStr(st, "/") > 0 Then
        st = 0
        numr = Left(st, InStr(st, "/") - 1)
        st = Left(st, Len(st) - InStr(st, "/"))
        denom = Left(st, InStr(st, """") - 1)
    Else
        inc = Left(st, InStr(st, """") - 1)
    End If
    ft2dec = Round(ft + (inc + numr / denom) / 12, 3)
End If
End Function
farrissfAuthor Commented:
Looks good, except it doesn't like non fractional numbers on the end.  If the measurement does not end with a fraction I have to put 0/2 or some other representation for 0,  Am enclosing the worksheet with several test so you can see.  I need to be able to put whole numbers in when neccesary.  I don't mind the zeros in the "ft" place or the zero in the "in" place; however I would prefer that the double prime symbol (") denote the end  of the measurement and then I could either add the (-) followed by a fraction of an inch if necessary followed my the (").

Other than that, this may be what I am looking for...
Converter-square-footage-v3.xlsm
Saqib Husain, SyedEngineerCommented:
This should let you do away with all zeros.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Offset(-1) = "Convert" Then
     If Target.Offset(, 3) = "X" Then
         Target.Offset(, 1) = ft2dec(Target)
     Else
         Target.Offset(, -1) = ft2dec(Target)
     End If
 End If
 End Sub

 Function ft2dec(x)
 st = x
 If InStr(st, "'") > 0 Then
     ft = Left(st, InStr(st, "'") - 1)
     st = Right(st, Len(st) - InStr(st, "'"))
 End If
 If InStr(st, """") > 0 Then
     If InStr(st, "-") > 0 Then
         inc = Left(st, InStr(st, "-") - 1)
         st = Right(st, Len(st) - InStr(st, "-"))
         numr = Left(st, InStr(st, "/") - 1)
         st = Right(st, Len(st) - InStr(st, "/"))
         denom = Left(st, InStr(st, """") - 1)
     ElseIf InStr(st, "/") > 0 Then
         st = 0
         numr = Left(st, InStr(st, "/") - 1)
         st = Left(st, Len(st) - InStr(st, "/"))
         denom = Left(st, InStr(st, """") - 1)
     Else
         inc = Left(st, InStr(st, """") - 1)
         denom = 1
     End If
     ft2dec = Round(ft + (inc + numr / denom) / 12, 3)
 End If
 End Function

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
farrissfAuthor Commented:
Works perfect!  Thank you.
farrissfAuthor Commented:
Just what I needed!  Thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.