How to parse in VBA a string that contains Mathematics special ASCII code and correct it

Question

Hi !

I'm currently writing a code in VBA to retrieve prices for some financial models. The problem I have is in the excel spreadsheet where I have special Ascii characters like ⅞ ¼ etc... I would need using VBA to transform this in 7/8 1/4 etc...

How could I do that ?

Thanks for your help

Answer 1

Create a lookup table. When it finds one of these special characters set the value appropriately (example "1/2" = 0.5) and if not use the Val() function to get the value.

Answer 2

If you want literal string replacement, use the Replace function thus:

Sub changeit()
    Dim w As Worksheet
    Dim r As Range
    For Each r In Application.Selection
        r.Value = Replace(r.Value, Chr$(188), "1/4")
        r.Value = Replace(r.Value, Chr$(189), "1/2")
        r.Value = Replace(r.Value, Chr$(190), "3/4")
    Next
End Sub

et cetera. (I have done each one separately to make it easier to read.) Alternatively, you could replace the literal strings "1/4" as @jalexiou suggests with 0.25 etc., ideally using a lookup table. Not sure what the Chr$ code is for the seven-eighths though.