Full Version: Imperial Units
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?
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.
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

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