Full Version: Imperial Units
UtterAccess Discussion Forums > Microsoft® Office > Microsoft Excel
kdw3
Hello,
I have a table of data I need to work with in Excel. I need to perform some math functions on cells that hold imperial units. For instance:
CODE
  
A                    B          C
2' - 4 1/2"      3/8"      10 1/4"

Oneed to be able to do something like subtract columns B and C from column A.
Is there a tool or procedure to working with these values without perfoming string seperation like the functions MID, LEFT and RIGHT?
Thank you for your time!
wmburgess
Not to my knowledge.
You could write a function to convert your cells to a number then do maths on result. Also you could write the reverse to redisplay sub result in your format.
Cheers.
Coldbrain
I believe you are definately going to have to do some mid/left/right/search functions to get what you want.. Particularly because in your example for A you have 2' .. excel doesn't seem to handle this kind of number.. you can input fractions without the " but it won't recognize the 2 feet 4 1/2 inches.. I'm sure this could be constructed with a formula..
Is there a reason you are avoiding the mid/left/right functions?
CB
kdw3
No reason, I was just hoping that Microsoft had something built in to handle this.
h well, I will go back to what I have been doing using the string functions.
Thank you for your time!
fecdad
Check this out:

CODE
Public Function ImpToDec(Imperial As String, Optional ReturnUnits As String = "Inches")
    Dim RetMultiplier As Integer
    
    Select Case ReturnUnits
        Case "Feet"
            RetMultiplier = 12
        Case "Inches"
            RetMultiplier = 1
        Case Else
            RetMultiplier = 1
    End Select
    
    Dim Constructor As String
    Constructor = ""
    
    Constructor = IIf(InStr(1, Imperial, "'"), "(", "") & Replace(Imperial, "'", ")*12")
    Constructor = Replace(Constructor, """", "")
    Constructor = Replace(Constructor, " ", "+")
    Constructor = Replace(Constructor, "-", "")
    
    ImpToDec = Evaluate(Constructor) / RetMultiplier
    
End Function

Use it on your worksheet:
=imptodec(cellreference)
default to inches returned
kdw3
Awesome!
The only thing is that it is not adding the inches from the feet to the inches given by the original quantity.
For instance: 1'-2 1/2" is being returned as 122.5 instead of 14.5 because the 2 after the dash is replacing the dash instead of adding to the 12.
If you come up with a solution, please let me know, otherwise I might try to figure this out.
Thank you very much!
fecdad
change this line:
Constructor = Replace(Constructor, "-", "")
to
Constructor = Replace(Constructor, "-", "+")
That will do it
I was assuming you had spaces before and after the - but you don't
I needed the same solution for the current project but couldn't find it - So I just started writing.
kdw3
That rocks!!!!
Thank you!
Luceze
I was thinking there would be a simple worksheet function to do the conversion (and there might be) but what I came up with was not so simple and will not work without the original inputs being formatted as such:
' - # #/#"
=IF(ISERROR(FIND("'",A1)),IF(ISERROR(FIND(" ",A1)),LEFT(A1,FIND("/",A1)-1)/MID(A1,FIND("/",A1)+1,(LEN(A1)-FIND("/",A1))-1),VALUE(TEXT(SUBSTITUTE(A1,"""",""),"0 #/#"))),LEFT(A1,FIND("'",A1)-1)*12+(--("0" & SUBSTITUTE(MID(A1,FIND("'",A1)+3,LEN(A1)-FIND("'",A1)+3),"""",""))))
The reason for the lengthiness is that Excel will convert a fraction to a date (3/8 becomes March 8) so you have to manually convert the fraction to a decimal.
fecdad
kdw3 - Glad I could help.
uceze - I was heading the same direction - then - after seeing the complexity, decided to go with code, which does seem to be easier to read and MUCH easier to reuse in other cells. Not to mention, you can choose to have your result returned in Feet instead of inches. Anyway - those people that are unfamiliar with VBA can use yours, Great to have both solutions listed.
Thanks for your input.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.