UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Imperial Units    
 
   
kdw3
post Jan 5 2007, 09:11 AM
Post #1

UtterAccess Guru
Posts: 512
From: Akron, Ohio
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"


I need 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!
Go to the top of the page
 
+
wmburgess
post Jan 5 2007, 09:36 AM
Post #2

UtterAccess Guru
Posts: 715
From: St Paul MN USA
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.
Go to the top of the page
 
+
Coldbrain
post Jan 5 2007, 09:37 AM
Post #3

UtterAccess Veteran
Posts: 484
From: Florida
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
Go to the top of the page
 
+
kdw3
post Jan 5 2007, 10:03 AM
Post #4

UtterAccess Guru
Posts: 512
From: Akron, Ohio
No reason, I was just hoping that Microsoft had something built in to handle this.

Oh well, I will go back to what I have been doing using the string functions.

Thank you for your time!
Go to the top of the page
 
+
fecdad
post Jan 25 2007, 02:06 AM
Post #5

UtterAccess Addict
Posts: 264
From: Allen, Texas
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
Go to the top of the page
 
+
kdw3
post Jan 25 2007, 07:29 AM
Post #6

UtterAccess Guru
Posts: 512
From: Akron, Ohio
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!
Go to the top of the page
 
+
fecdad
post Jan 25 2007, 09:28 AM
Post #7

UtterAccess Addict
Posts: 264
From: Allen, Texas
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.
Go to the top of the page
 
+
kdw3
post Jan 25 2007, 09:32 AM
Post #8

UtterAccess Guru
Posts: 512
From: Akron, Ohio
That rocks!!!!

Thank you!
Go to the top of the page
 
+
Luceze
post Jan 25 2007, 09:45 AM
Post #9

UtterAccess VIP
Posts: 2,601
From: Dallas, Texas USA
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.
Go to the top of the page
 
+
fecdad
post Jan 25 2007, 09:55 AM
Post #10

UtterAccess Addict
Posts: 264
From: Allen, Texas
kdw3 - Glad I could help.

Luceze - 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.
Go to the top of the page
 
+
« Next Oldest · Microsoft Excel · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 20th May 2013 - 09:03 AM