OzGrid

How to convert decimals to text fraction

Category: [Excel]  Demo Available

How to convert decimals to text fraction

Requirement:

The user enters data as a decimal for example 20.25.

The user would like to covert that to 20 1/4 in text form so that the user can copy and add it to the description in a plywood cut program.

Solution:

Code:
```Sub JohnDrew()
Dim x, y As Double, z
Dim r As Range
For Each r In Selection
x = Split(r.Value, ".")
Select Case Len(x(1))
Case Is = 1
y = (CLng(x(1)) / 10)
z = Excel.WorksheetFunction.Text(y, "# ?/??")
z = IIf(x(0) <> "0", x(0) & z, z)
Case Is = 2
y = (CLng(x(1)) / 100)
z = Excel.WorksheetFunction.Text(y, "# ?/??")
z = IIf(x(0) <> "0", x(0) & z, z)
Case Is = 3
y = (CLng(x(1)) / 1000)
z = Excel.WorksheetFunction.Text(y, "# ?/??")
z = IIf(x(0) <> "0", x(0) & z, z)
End Select
r.Offset(, 1).NumberFormat = "@"
r.Offset(, 1).Value = Trim(z)
Next r
End Sub```

Obtained from the OzGrid Help Forum.

Solution provided by skywriter.

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

Gallery

stars (0 Reviews)