How can I convert numbers with "subscript" fractions to decimal in Excel?

Question

I have thousands of numbers that were given to me in an unusable format for calculations:

9⅝, 9¼, 9¾, 10¼

This is how they appear in each row in Excel. I would like to convert these to a decimal. I've tried =FIXED(A2, 3) but that did not do the trick. The problem arises because the fractions are in this weird format.

Is there a formula or macro that I can implement to get the desired decimal results?

Thank you very much and happy Easter!

Answer 1

The problem is that each of your fractions is a single unicode character. If we isolate a single character in a cell and run:

Sub WhatsIt()
    Dim v As Variant
    v = ActiveCell.Value
    MsgBox Asc(v) & vbCrLf & AscW(v) & vbCrLf & Application.WorksheetFunction.Dec2Hex(AscW(v))
End Sub

we see:

It now becomes easy to convert these items into real values. Here is a start:

Public Function Konvert(s As Variant) As Double
    Dim d As Double

    d = CDbl(Mid(s, 1, Len(s) - 1))
    If AscW(Right(s, 1)) = 8541 Then d = d + 0.625
    Konvert = d
End Function

You must expand this code to include other fractions. Here are some common ones: