PDA

View Full Version : can anyone help me convert in this formate 8' -5/8" into 100.625 inchs

iki
11-07-2008, 08:36 AM
Hi, in my work i am constantly having to change a dimention give to me like 8'-5 5/8", 4'-0 5/8" etc. I have tryied using the formula

=(((LEFT(A1,SEARCH("'",A1)-1))*12)+1)+(CLEAN(RIGHT(SEARCH("'",A1)+1,2)))

but it is not working out and my numbers are off, if anyone can give me some advice i would be greatfull :)

Tommy
11-07-2008, 09:03 AM
Hi iki, :hi:

Welcome to VBAX!

I have 2 solutions, the formula is not by me:

=IF(ISBLANK(H10),"",IF(ISERROR(FIND("-",H10,1)),H10, IF(ISBLANK(H10),"",(LEFT(H10,FIND("'",H10)-1)*12)+(MID(H10,(FIND("-",H10))+1,2))+IF(ISERROR(MID(H10,(FIND("/",H10)),1)),0,((MID(H10,(FIND("/",H10)-2),2)/(MID(H10,(FIND("/",H10)+1),2))))))))

This would be used as such umm the H10 is the cell that has the number.

=IF(ISBLANK(H10),"",GenDec(H10))

Place this in a module in the workbook.

Public Function GenDec(FtInch As String) As String
Dim mFeet As Integer
Dim mInch As Integer
Dim mFract As Double
Dim mI As Integer
Dim mHldStr As String
Dim mIncSix As Variant
mHldStr = Replace(FtInch, Chr(34), "")
mHldStr = Trim(Replace(mHldStr, " ", " "))
FtInch = mHldStr & Chr(34)
mFeet = Val(mHldStr)
mI = InStr(1, mHldStr, "'-")
If mI = 0 Then mI = InStr(1, mHldStr, "'")
If mI > 0 Then
mHldStr = Replace(Mid(mHldStr, mI + 1), "-", vbNullString)
Else 'means no feet
mFeet = 0
End If
mHldStr = Trim(mHldStr)
mIncSix = Split(mHldStr, " ")
mInch = Val(mIncSix(0))
If UBound(mIncSix) > 0 Then
mI = InStr(1, mIncSix(1), "/")
mFract = Val(mIncSix(1)) / Val(Mid(mIncSix(1), mI + 1))
ElseIf InStr(1, mIncSix(0), "/") > 0 Then
mInch = 0
mI = InStr(1, mIncSix(0), "/")
mFract = Val(mIncSix(0)) / Val(Mid(mIncSix(0), mI + 1))
End If
GenDec = Format(mFeet * 12 + mInch + mFract, "########0.00")
End Function

iki
11-07-2008, 09:31 AM

just tried this:

=IF(ISBLANK(H10),"",IF(ISERROR(FIND("-",H10,1)),H10, IF(ISBLANK(H10),"",(LEFT(H10,FIND("'",H10)-1)*12)+(MID(H10,(FIND("-",H10))+1,2))+IF(ISERROR(MID(H10,(FIND("/",H10)),1)),0,((MID(H10,(FIND("/",H10)-2),2)/(MID(H10,(FIND("/",H10)+1),2))))))))

but i am getting an error, in the cell i have 8'-4 5/8" and #VALUE! is output

Also tried second method but i must be doing it wrong, get value of #NAME?

lucas
11-07-2008, 09:46 AM
I use John Lachers functions:

http://www.lacher.com/examples/lacher18.htm

Hey Tommy.....all well down south?

iki
11-07-2008, 10:12 AM
Ahh thank you very much that is exactly what i need.

one more quick question. How do you view the modules? using excel 2007

Tommy
11-07-2008, 10:20 AM
On the formula you have to have a space between the " and the last number. Like I said I didn't write it.

You need to enable macros. Colse the workbook and reopen it and pick enable macros if this doesn't work you will need to change you security settings.

Hey Steve we have a lot and I mean a LOT of firewood laying all over the place.

lucas
11-07-2008, 10:28 AM
Tommy, 10/4 on the firewood. They are giving it away up here. We had severe tree damage from a huge ice storm last winter. It did severe damage to 6 pecan trees that I had grafted when the kids were babies so they were 30 years old......it broke my heart.

Did you have any flooding problems?

iki, when you get your solution here be sure to mark your thread solved using the thread tools at the top of the page.

xld
11-07-2008, 10:36 AM
Just use this formula

=(LEFT(H10,SEARCH("'",H10)-1)*12)+MID(H10,SEARCH("-",H10)+1,LEN(H10)-SEARCH("-",H10)-1)

it will default the cell format to 'l m/n', so you will need to set the cell format yourself.

lucas
11-07-2008, 10:43 AM
That works great Bob......what formula to reverse...go from decimal to fraction?

xld
11-07-2008, 10:49 AM
=INT(L10/12)&"'-"&TEXT(MOD(L10,12),"? ?/?")&""""

xld
11-07-2008, 10:51 AM
Correction, to cater for no inches and no fractions

=INT(L10/12)&"'-"&IF(MOD(L10,12)=0,"0""",TEXT(MOD(L10,12),"# #/#")&"""")

xld
11-07-2008, 10:54 AM
One more modification to cater for fractions and no whole inches

=INT(L10/12)&"'-"&IF(MOD(L10,12)=0,"0""",TEXT(MOD(L10,12),"0 #/#")&"""")

xld
11-07-2008, 10:55 AM
And actually, that last change means I can do away with the IF

=INT(L10/12)&"'-"&TEXT(MOD(L10,12),"0 #/#")&""""

lucas
11-07-2008, 10:57 AM
That works great too.
That is very useful Bob. Thanks for that. I work with fractions all the time so this will be a big help.

How would you incorporate isserror so that if there is no data inputed into the referenced cell you don't get the dreaded #VALUE! error?

lucas
11-07-2008, 11:02 AM
Another question, this rounds to the nearest 1/8". Can it be changed to deal with 1/16"....

I don't see the connection to the tolerance.........could you point that out please?

xld
11-07-2008, 11:16 AM
Steve,

This handles 16ths, 32nds, 64ths

=INT(L10/12)&"'-"&TEXT(MOD(L10,12),"0 #/##")&""""

Re the \VALUE error, do you mean abslutely no value?

Is the question about tolerance directed at me?

lucas
11-07-2008, 11:38 AM
Steve,

This handles 16ths, 32nds, 64ths

=INT(L10/12)&"'-"&TEXT(MOD(L10,12),"0 #/##")&""""

Re the \VALUE error, do you mean abslutely no value?

Is the question about tolerance directed at me?

The question about tolerance was directed to you Bob but you have addressed it by adding the extra # to the formula.

With the new formula my returns now shows 0'-0" when the referenced cell is empty, which is perfectly acceptable.

You have given me a new and much easier way to deal with fractions today and I want to thank you for your generosity. It is much appreciated.

ps it not only handles 16ths, 32nds, 64ths.........it also handles 10ths, etc.

this is solved for my part.

:beerchug:

Tommy
11-07-2008, 11:38 AM
Wow Steve you stepped off into that one eh?

Bob 1/16" would be close enough. Not many people can cut an I beam to a 1/64" (0.3969mm?) my hand shakes too bad anymore.

lucas
11-07-2008, 11:47 AM
Yeah Tommy but it was worth getting clipped for. I'll always be humble when I can learn something useful.

It's hard to cut within 1/16 with a propane torch with a number 2 cutting tip for sure.

I used to cut some pretty fine designs with a small victor torch with acetylene fuel and a 00 tip......not so much these days.

Mikey
11-08-2008, 04:37 AM
=INT(L10/12)&"'-"&TEXT(MOD(L10,12),"0 #/##")&""""

Perhaps it isn't likely but if L10 is 23.999, for instance, then this formula would return 1' 12""

I find that it's better to round to the smallest unit required, e.g. to always round to the nearest 1/64

=INT(ROUND(L10*64,0)/64/12)&"'-"&TEXT(MOD(ROUND(L10*64,0)/64,12),"0 #/##")&""""

substitute 16 for 64 if you want to round to the nearest 1/16