Excel Data Imported as '15/2, Macro to convert to number?

Question

I'm importing data from an API into Excel via power query.

As some of the data is in fractions this is causing a bit of a nightmare. I can't use 'number' because of this and it throws an error, so I have to import as text.

When doing so the data imports as below, with a "'" in front:

As I need the data to update each minute, and then run formulas on it, I need this to be a number. What's the easiest way (either macro to convert to number from '15/2) or to fix Power Query and allow for it to import/convert fractions? (That would be perfect!)

Answer 1

Here's a macro to use (assuming your screenshot has the correct column placements). The only adjustment you need to make is adjust "intLastRow" with the last row of your data.

Sub SO()
    Dim intLastRow As Integer, strIn As String
    intLastRow = 100
    For I = 2 To intLastRow
        For t = 4 To 21
            If InStr(1, Cells(I, t).Value, "/") > 0 Then
                strIn = Cells(I, t).Value
                Cells(I, t).Value = Val(Left(strIn, InStr(1, strIn, "/") - 1)) / Val(Right(strIn, Len(strIn) - InStr(1, strIn, "/")))
            Else
                Cells(I, t).Value = Val(Cells(I, t).Value)
            End If
        Next t
    Next I
End Sub

Answer 2

You can then step through a selection of cells and check to see if the prefix character for each cell is an apostrophe. If it is, then all you need to do is have the macro do the equivalent of manually retyping the contents of the cell, in the following manner:

For Each c In Selection
    If c.PrefixCharacter = "'" Then
        c.Value = c.Value
    End If
Next c

Note that the macro checks what is in the PrefixCharacter property. This property can be read in VBA, but it cannot be changed directly. That is why the macro needs to use the seemingly simple line to assign the value of each cell back into the cell—essentially retyping the contents.

For complete guide see this: http://excel.tips.net/T003332_Searching_for_Leading_Apostrophes.html

This is my own code:

Public Sub tryit()
Dim i As Long
For i = 1 To 20 'repeat until your last record
    With ThisWorkbook.Sheets("Sheet1")
        If .Cells(i, 5).PrefixCharacter = "'" Then
            MsgBox "removing 1 apostrophe..."
            .Cells(i, 5).FormulaR1C1 = "=" & .Cells(i, 5)
        End If
    End With
Next i
End Sub

And... Voila!

Answer 3

The Power Query solution to parsing math expressions as a number is simple!

Just add a custom final step Table.TransformColumns(SomeLastStep, {}, Expression.Evaluate). This works by running text input like 9 or 15/2 as a tiny program that returns a number.

(You might also want to convert the columns to number afterwards.)

View > Advanced Editor for full example:

let
    SomeLastStep = #table( {"data1", "data2"}, { {"9", "15/2"}, {"10", "8"}, {"11", "10"}, {"11", "10"} } ),
    Custom1 = Table.TransformColumns(SomeLastStep, {}, Expression.Evaluate),
    #"Changed Type" = Table.TransformColumnTypes(Custom1,{{"data1", type number}, {"data2", type number}})
in
    #"Changed Type"

---

BTW, this is different than the dreaded eval() in scripting languages: without any arguments, Expression.Evaluate will not have access to any functions in scope and so can't have any side effects.

Answer 4

perhaps this is the macro vba code you want to see for your another problem:

Public Sub tryit()
    Dim i As Long
    For i = 1 To 20 'repeat until your last record
        With ThisWorkbook.Sheets("Sheet1")
            .Cells(i, 5).FormulaR1C1 = "=" & .Cells(i, 5)
        End With
    Next i
End Sub