Thanks:  0
Likes:  0

# Thread: Converting to fractional inches (follow-up Q)

1. I agree with yogi.. for differentiating between 2.1 and 2.10.

actually yogi we in civil engineer spell like

2 foot 1 inch.. i.e 2.1

2 foot 10 inch l.e 2.10

now this something unavoidable in practical use.

because computer treats 2.1 as 2 and 10 same way it treats 2.10 as 2 and 10

well this some times creats a problem for me in designs and estimates.

well i tried to find out the lenght of the value in a cell..say if it is general or text formatted

2.1 will return 3 i.e the lenght of the string
2.10 will return 4.

now this somehow does not work in vba code i tried to us string functions also and then decided people should enter 2.10 .. 2.01 etc.
ni****h desai
nisht@pexcel.com
http://www.pexcel.com

2. Friend,

I have able to solve your problem..

here function fractionalinch can due the convertion on numberic values.. it will convert

2.1 to 2.83, 2.2 to 2.17 etc

if you write

2.01 then to 2.83, 2.02 to 2.17 etc..

so it depends on you..

my adive to you is to give entry as

2 foot 1 inch as 2.01 ....
2 foot 2 inch as 2.02
'
'
2 foot 10 inch as 2.10

; this is the correct way to give entry...

okay ..

now to your solution.. see another function...fractionalpart.

now this is the thing what exactly you wanted.

all you need to do is to format your cells to text and then use this function.

Function fractionalinch(x As Single) As Single
Dim sngfrac As Single
Dim sngvalue As Single

sngvalue = Fix(x)
x = (x - sngvalue) * 100
sngfrac = x
If sngfrac <= 12 Then
sngfrac = sngfrac
Else
sngfrac = sngfrac / 10
End If

fractionalinch = Round((sngfrac / 12), 2)
fractionalinch = sngvalue + fractionalinch
End Function

Function fractionalpart(x As Range)
Dim strright As String
Dim strleft As String

If x.NumberFormat <> "@" Then
Exit Function
End If

strleft = Left(x, InStr(x, ".") - 1)
strright = Right(x, Len(x) - InStr(x, "."))
fractionalpart = CSng(strleft) + Round(CSng(strright) / 12, 2)
End Function

ni****h desai
http://www.pexcel.com

3. Hi All,

Numerous threads have been done on this topic, but there is no way to distinguish between 2.1 and 2.10 unless the cells are formatted as text. If numeric formatting is required, 2.1 must go to 2.01 for this to work properly.

If these are text values, most of these will work.

Yogi, your formula is correct for the values (with the usual caveats already discussed) except for 2.0 (or 2). Then, it it gives erroneous results.

Bye,
Jay

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•