Converting to fractional inches (follow-up Q)

StevenData

New Member
Joined
Mar 23, 2002
Messages
10
Greetings,
I am trying to convert a column of lengths in the numeric format of decimal feet (i.e. 2.1, 2.2,…2.10,12.11). how can I convert this to fractional inches(i.e. 2.08, 2.17,…,2.83,2.92). A great contributor of this forum,dk has suggested using the following macro, which beautifully works for all values except those containing the fractions .10 and .11(i.e. 2.10, 2.11). could you please help me to make dk’s macro work for all valid values.

sub convertfeetandinches()
dim cl as range
for each cl in selection.cells
if isnumeric(cl.value) then
cl.value=format(int(cl.value)+(cl.value - int(cl.value))*10/12,"#,##0.00")
end if
next
end sub
Sincerely, Steve
 
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
 
Upvote 0

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top