Convert decimal to text fraction

JohnDrew · Jun 3rd 2016

I enter data as a decimal. Like 20.25
I would like to covert that to 20 1/4 in text form so I can copy and add it to the description in a plywood cut program.
I can change the format to factions in Excel, but when I copy and paste it reverts back to decimal. The most would have 3 decimals like .125

crushdrinker06 · Jun 3rd 2016

Re: Convert decimal to text fraction

Try using a custom format of # #/# . It worked when going to word and notepad.

skywriter · Jun 3rd 2016

Re: Convert decimal to text fraction

See if this is something you can use.

Highlight A1:A6 before running the macro.

Here's the code, maybe not the best, but it seems to work.

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

Display More

JohnDrew · Jun 4th 2016

Re: Convert decimal to text fraction

Thank you. That worked

Convert decimal to text fraction

Participate now!

Share