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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I am not a programmer and I did not see the original question. You can consider the following:

2.01 2.0833 1/12 0.083
2.02 2.1667 1/6 0.167
2.03 2.2500 1/4 0.250
2.04 2.3333 1/3 0.333
2.05 2.4167 5/12 0.417
2.06 2.5000 1/2 0.500
2.07 2.5833 7/12 0.583
2.08 2.6667 2/3 0.667
2.09 2.7500 3/4 0.750
2.1 2.8333 5/6 0.833
2.11 2.9167 11/12 0.917

revised line

cl.Value = Format(Int(cl.Value) + (cl.Value - Int(cl.Value)) * 100 / 12, "#,##0.0000")
This message was edited by Dave Patton on 2002-03-30 21:21
 
Upvote 0
Your problem is that it is treating the value to the right of the decimal place as decimal format. This is thrown for a loop with 11 and 12 inches.

Try this:
cl.value=format(int(cl.value)+ if(Int(cl.value)<>cl.value,Right(cl.value,Find(".",cl.value)-1)/12,0),"#,##0.00")
 
Upvote 0
Hello Steven,

Sorry about the error in my original post. Here is some revised code which produces the following results:-

Before After
2.1 2.08
2.2 2.17
2.3 2.25
2.4 2.33
2.5 2.42
2.6 2.50
2.7 2.58
2.8 2.67
2.9 2.75
2.10 2.83
2.11 2.92
2.12 3.00

Code:
Sub ConvertFeetAndInches()
Dim cl As Range
For Each cl In Selection.Cells
    If IsNumeric(cl.Value) Then
        cl.Value = Format(Int(cl.Value) + Right(cl.Value, Len(cl.Value) - InStr(1, cl.Value, ".")) / 12, "#,##0.00")
    End If
Next
End Sub

HTH,
D
 
Upvote 0
See if this function can be useful to you..

this function was written by damon... and i got it from a questioner.

I have added few codes to make it useable for negative values.
Actually this is for converting for inches to foot.
working with inches is always a problem with us on the site.

metric is best system... still we work on inches.

Function FormatFeetInches(r1 As Range, Optional SmallestFrac As Integer = 16) As String

' DecimalIn is the number of inches to be expressed as a whole part and
'fraction thereof
' Format will display the result in string format, reducing the
'Denominator
' to the smallest possible within the constraints of bringing the number
'to the nearest
' sixteenth. The optional parameter SmallestFrac allows you to set the
'largest
' denominator to something other than sixteenths (set to 8 for eights,
'etc.). The resulting
' number will be displayed in the format 2'-7 3/8"

Dim WholePart As Integer
Dim FractPart As Single
Dim Numerator As Integer
Dim Denominator As Integer
Dim Feet As Integer
Dim flag As Boolean

DecimalIn = r1.Value
'Debug.Print R1.Address
If DecimalIn< 0 Then
DecimalIn = Abs(DecimalIn)
flag = True
End If

WholePart = Fix(DecimalIn)
If WholePart< 12 Then
Feet = 0
Else
Feet = WholePart 12
WholePart = WholePart Mod 12
End If
FractPart = DecimalIn - Fix(DecimalIn)
Denominator = SmallestFrac
Numerator = Fix(FractPart * SmallestFrac + 0.5) '0.5 Rounds to nearest
'16 th

' Reduce numerator and denominator by factors of 2
Do Until Numerator / 2<> Numerator 2 Or Numerator< 2
Denominator = Denominator / 2
Numerator = Numerator / 2
Loop

' Format the result into a string
If Numerator<> 0 Then
FormatFeetInches = CStr(WholePart) & " " & CStr(Numerator) & "/" & CStr(Denominator) & """"
Else
FormatFeetInches = CStr(WholePart) & """"
End If

If Feet<> 0 Then
FormatFeetInches = CStr(Feet) & "'-" & FormatFeetInches
End If

If flag = False Then
FormatFeetInches = FormatFeetInches
Else
'r1.Interior.ColorIndex = 12
FormatFeetInches = "-" & FormatFeetInches
End If
End Function

I hope this can work for you.

ni****h desai
http://www.pexcel.com
This message was edited by nisht on 2002-03-31 02:59
 
Upvote 0
Attention DK

I get the same result with your formula as the formula I quickly edited except I get a different result for 2.10.
Please check the formula with 2.10; both 2.01 and 2.10 give 2.08.
 
Upvote 0
Dave,

I think it was established in Steven's earlier post that the cells containing the data would be formatted as text - that is the assumption I've made. If the values are going to be formatted as numbers then the code wouldn't work and I think it could be tricky to do. I guess that you'd need to check the NumberFormat property of the cell and go from there but I'll wait and see what Steven says before doing anything else.

Regards,
Dan.
 
Upvote 0
There's a function in the Analysis Tool Pack that does this. DOLLARDE(A1,12) will convert 2.06 (2 ft 6 inches) to 2.5. DOLLARFR()will convert it back You can uscustom formatting in your cell 00 "ft".00 "in" to make display 2 ft. 06 in when you enter 2.06
 
Upvote 0
Hi StevenData:
In response to an earlier posting by you on the same topic, where we had quite a lively discussion, my final post was:

2 ft&in.00 =INT(B21)+RIGHT(B21,2)/12
2 ft&in.01 2.08
2 ft&in.02 2.17
2 ft&in.03 2.25
2 ft&in.04 2.33
2 ft&in.05 2.42
2 ft&in.06 2.50
2 ft&in.07 2.58
2 ft&in.08 2.67
2 ft&in.09 2.75
2 ft&in.10 2.01
2 ft&in.11 2.92
2 ft&in.12 3.00

StevenData, please note that I have used two digit representation for inches -- otherwise you run into problems differentiating between 2.1 and 2.10


Looks like you still do not have an answer to your liking. Please post what is it that does not work for you and then let us take it from there!


_________________
Yogi Anand
Edit: Deleted inactive web site reference from hard code signature line
This message was edited by Yogi Anand on 2003-01-19 17:41
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

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