Excel Data Imported as '15 / 2, Macro to Convert to Number?
I am importing data from API to excel using power request.
Since some of the data is in fraction, this is a bit of a nightmare. Because of this, I cannot use "number" and it throws an error, so I need to import as text.
This will import the data as shown below with "'" infront:
Since I need data to update every minute and then run formulas on it, I need that number. The easiest way (either convert the macro to a 15/2 number) or fix Power Query and let it import / convert fractions (that would be perfect!).
Many thanks
source to share
A macro is used here (assuming your screenshot has the correct column placement). The only thing you need to do is set up "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
source to share
Then you can sample the cells and check if the prefix character for each cell is an apostrophe. If so, then all you have to do is make the macro equivalent to manually entering the contents of the cell like this:
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 cannot be changed directly. This is why the macro needs to use a seemingly simple line to assign the value of each cell back to the cell - essentially retyping the content.
For a 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!
source to share
Power Query's solution to parse math expressions like numbers is crazy easy!
Just add a custom end step Table.TransformColumns(SomeLastStep, {}, Expression.Evaluate)
. It works by triggering a text type input, 9
or 15/2
as a tiny program that returns a number.
(You might also want to convert columns to numbers.)
View> Advanced Editor for a complete 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"
source to share