Apply a VBA function to millions of cells in an Excel file

[jaouadaoudi]

Hi ,

I am analyzing a database of almost a million rows (Excel file), and I want to apply a function "ExtrationPercentage" developed by VBA. This function extracts the percentages of a text cell.

So I follow the following procedure: I copy the formula, then I paste it on a range of cell.

The approach works well on few hundreds rows, but when I apply it to almost a million rows, (i) the execution of the function becomes heavy (almost 40 minutes of treatment); and (ii) the result becomes unreliable (no results, rather display of "#VALUE!" in all the cells containing the function.

For test purposes, I send a file that shows some rows of my database with the VBA code of function "ExtrationPercentage". This will give you a clear idea about my problem. Here is the link of the Excel file:

https://www.cjoint.com/c/HFBtZKBuGFP

So, my question is:

Please, do you have an approach, a procedure, a VBA code, or a Power BI solution that could make it easier for me to apply my function to my entire database?

I just learned today about the existence of Power BI by a member of Microsoft community, and I don't know if it could help me solve my problem.

Thank you very much for your involvement and for your support.

Jad

Reminder: VBA code used for the function "ExtrationPercentage"(Developed by Arnaud "Hecatonchire", a member of the microsoft community )

====================================================

Function ExtrationPourcentage(Chaine As String, k As Byte) As Single
    Dim reg As Object
    Dim Resultats As Object
    Dim Resultat As String
    
    Set reg = CreateObject("vbscript.regexp")
    reg.Global = True
    
    reg.Pattern = "\d+\.?\d*\%"
    Set Resultats = reg.Execute(Chaine)
    If Resultats.Count <> 0 Then
        Resultat = Replace(Replace(Resultats.Item(k - 1), "(", ""), "%", "")
    End If
    ExtrationPourcentage = (Replace(Resultat, ".", ",") / 100)
End Function

====================================================

[jaouadaoudi]

Hi ,

I am analyzing a database of almost a million rows (Excel file), and I want to apply a function "ExtrationPercentage" developed by VBA. This function extracts the percentages of a text cell. So I follow the following procedure: I copy the formula, then I paste it on a range of cell.

The approach works well on few hundreds rows, but when I apply it to almost a million rows, (i) the execution of the function becomes heavy (almost 40 minutes of treatment); and (ii) the result becomes unreliable (no results, rather display of "#VALUE!" in all the cells containing the function.

For test purposes, I send a file that shows some rows of my database with the VBA code of function "ExtrationPercentage". This will give you a clear idea about my problem. Here is the link of the Excel file:

https://www.cjoint.com/c/HFBtZKBuGFP

So, my question is:

Please, do you have an approach, a procedure, a VBA code, or a Power BI request that could make it easier for me to apply my function to my entire database? Or to resolve my percentage extraction problem for all my database? 

I just learned today about the existence of Power BI by a member of Microsoft community, and I don't know if it could help me solve my problem. What is your opinion/suggestion?

Thank you very much for your involvement and for your support.

Jad

Reminder: VBA code used for the function "ExtrationPercentage"(Developed by Arnaud "Hecatonchire", a member of the microsoft community )

====================================================

Function ExtrationPourcentage(Chaine As String, k As Byte) As Single
    Dim reg As Object
    Dim Resultats As Object
    Dim Resultat As String
    
    Set reg = CreateObject("vbscript.regexp")
    reg.Global = True
    
    reg.Pattern = "\d+\.?\d*\%"
    Set Resultats = reg.Execute(Chaine)
    If Resultats.Count <> 0 Then
        Resultat = Replace(Replace(Resultats.Item(k - 1), "(", ""), "%", "")
    End If
    ExtrationPourcentage = (Replace(Resultat, ".", ",") / 100)
End Function

====================================================

[JP-Ronse]

Hi Jad,

I played a bit further with PQ and came to following:

let
    Source = Excel.CurrentWorkbook(){[Name="Table24"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Perform", type any}}),
    #"Removed Blank Rows" = Table.SelectRows(#"Changed Type", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
    #"Added Custom" = Table.AddColumn(#"Removed Blank Rows", "Custom", each Text.Remove([Perform],List.Transform({33..44,47,59..126}, each Character.FromNumber(_)))),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Trim([Custom],":")),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if Text.PositionOf([Custom.1],":")+1 >0 then Text.Range([Custom.1],Text.PositionOf([Custom.1],":")+1) else [Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each Text.Trim([Custom.2], {"."," "})),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each Text.Replace(Text.Replace(Text.Replace(Text.Replace([Custom.3],"  "," "),"  "," ")," "," "),". "," ")),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Added Custom4","Custom.4",Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),{"Custom.4.1", "Custom.4.2", "Custom.4.3", "Custom.4.4", "Custom.4.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Custom.4.1", type number}, {"Custom.4.2", type number}, {"Custom.4.3", type number}, {"Custom.4.4", type number}, {"Custom.4.5", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Custom", "Custom.1", "Custom.2", "Custom.3"})
in
    #"Removed Columns"

Table24 is the Perform column. Needs further tweaking, is only a starting point and pretty sure that someone like Marcel Beug will come with a much better approach.