Getting a sum of a list in excel

Question

I have an excel cell that has a value like this:

[194, 294, 294, 294]

I am trying to get the sum of this cell which is a value generated with python in a list structure.

I have not found any questions on this problem. I tried =SUM(A2) but for some reason, it returns 0 instead of a an error which I expected.

Answer 1

To sum the elements of a Python-style list in an Excel cell use a VBA User-Defined Function (UDF):

1. Press ALT + F11 to open the VBA editor.
2. Insert a new module by right-clicking on any of the items in the Project Explorer window, selecting Insert, and then Module.
3. Copy and paste the following VBA code into the module:

`

Function SumPythonList(cell As Range) As Double
    Dim listString As String
    Dim numbers As Variant
    Dim i As Integer
    Dim total As Double
    
    ' Get the cell value and remove the square brackets
    listString = Replace(Replace(cell.Value, "[", ""), "]", "")
    
    ' Split the cleaned string by commas
    numbers = Split(listString, ",")
    
    ' Initialize total
    total = 0
    
    ' Sum the numbers
    For i = LBound(numbers) To UBound(numbers)
        total = total + Val(Trim(numbers(i)))
    Next i
    
    ' Return the sum
    SumPythonList = total
End Function

4. Close the VBA editor.

5. Use the new function in Excel. In a cell, use the formula: =SumNumbersInCell(A1)

Answer 2

Try-

=SUM(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"[",""),"]",""),", ","</s><s>")&"</s></t>","//s"))