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

[iki]

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]

Hi iki,

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.
[vba]
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
[/vba]

[iki]

Thank you for your quick reply

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]

I use John Lachers functions:

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


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

[iki]

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]

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]

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.

[Bob Phillips]

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]

That works great Bob......what formula to reverse...go from decimal to fraction?

[Bob Phillips]

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

[Bob Phillips]

Correction, to cater for no inches and no fractions

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

[Bob Phillips]

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 #/#")&"""")

[Bob Phillips]

And actually, that last change means I can do away with the IF

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

[lucas]

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]

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?

[Bob Phillips]

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]

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.

[Tommy]

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]

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]

=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