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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to vertically centre text in userform label |
| How to use a dropdown or text option |
| How to make all empty textboxes invisible |
| How to add frequently used text to a cell |
| How to copy cell value on Sheet 1 to a location in a text in another cell on Sheet 2 |
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.