# Fraction Format (Re-Explained)

#### ToddG

##### Board Regular
I'm really sorry for the repost on this but i'm getting desperate on this one. I know it's probably pretty simple but obviously i'm no expert here :wink:

Hopefully this explains it better:

The attached code is joining the values of two cells (in columns B & C) and putting an "x" between them (not multiplying, just an "x")

Example:

5.125 x 1.5

What I need is the decimal values to show as fractions in this string

Example:

5 1/8 x 1 1/2

If anyone could take a look i'd REALLY appreciate it!

Code:

For Each c In Range("D:D")
If c.Value = "Total" Then
c.Offset(0, 1) = Cells(c.Row, "b") & " x " & Cells(c.Row, "c")
End If
Next c

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Hi ,

Function FormatFraction(DecimalNumber As Single, Optional SmallestFrac As Integer = 16) As String

' DecimalNumber is the number 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.)

Dim WholePart As Integer
Dim FractPart As Single
Dim Numerator As Integer
Dim Denominator As Integer

WholePart = Fix(DecimalNumber)
FractPart = Abs(DecimalNumber - WholePart)
Denominator = SmallestFrac
Numerator = Fix(FractPart * SmallestFrac + 0.5) '0.5 Rounds to nearest 16th

' 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
If Denominator = 1 Then
FormatFraction = CStr(WholePart + Sgn(DecimalNumber))
Else
FormatFraction = CStr(WholePart) & " " & CStr(Numerator) & "/" & CStr(Denominator)
End If
Else
FormatFraction = CStr(WholePart)
End If

End Function

Then you could accomplish what you describe via:

For Each c In Range("D:D")
If c.Value = "Total" Then
c.Offset(0, 1) = FormatFraction(Cells(c.Row, "b")) & " x " & FormatFraction(Cells(c.Row, "c") )
End If

Note that the default is for the smallest fraction reduction to be in sixteenths, but that you can make it eighths or whatever by setting it like this:

c.Offset(0, 1) = FormatFraction(Cells(c.Row, "b"),8) & " x " & FormatFraction(Cells(c.Row, "c") ,8)
Next c

Thanks a MILLION Damon. Works great.

One minor little thing. If a value (the fraction) is less than 1 it leaves a 0 for the whole number.

Example: 0 3/4

Would it be an easy fix to eliminate this 0 for values less than 1?

ToddG said:
Thanks a MILLION Damon. Works great.

One minor little thing. If a value (the fraction) is less than 1 it leaves a 0 for the whole number.

Example: 0 3/4

Would it be an easy fix to eliminate this 0 for values less than 1?
Hi ToodG:

In addition to Damon's UDF, you may also want to try the following code ...
Code:
``````Sub y_3()
For Each cell In Range("B2:B" & Range("B65536").End(xlUp).Row)
If cell <> "" Then cell(1, 3) = Evaluate("=TEXT(" & cell.Value & ",""# ?/??"")&"" x "" & TEXT(" & cell(1, 2).Value & ",""# ?/??"")")
Next cell
End Sub``````

Hi ToddG,

To get rid of the 0 in my code simply replace the code:

FormatFraction = CStr(WholePart) & " " & CStr(Numerator) & "/" & CStr(Denominator)

with:

FormatFraction = IIf(WholePart = 0, "", CStr(WholePart) & " ") & CStr(Numerator) & "/" & CStr(Denominator)

Replies
7
Views
167
Replies
1
Views
241
Replies
0
Views
165
Replies
7
Views
250
Replies
3
Views
263

### Forum statistics

1,203,241
Messages
6,054,327
Members
444,717
Latest member
melindanegron ### 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.

### Which adblocker are you using?    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

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