kdw3

Jan 5 2007, 09:11 AM

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

Jan 5 2007, 09:36 AM

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

Jan 5 2007, 09:37 AM

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

Jan 5 2007, 10:03 AM

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

Jan 25 2007, 02:06 AM

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

Jan 25 2007, 07:29 AM

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

Jan 25 2007, 09:28 AM

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

Jan 25 2007, 09:32 AM

That rocks!!!!

Thank you!

Luceze

Jan 25 2007, 09:45 AM

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

Jan 25 2007, 09:55 AM

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.