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

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

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

11-07-2008, 09:46 AM

I use John Lachers functions:

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

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

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.

Bob Phillips

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?

Bob Phillips

11-07-2008, 10:49 AM

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

Bob Phillips

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

Bob Phillips

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

Bob Phillips

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?

Bob Phillips

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.