CODEWITHSUNDEEP
libreofficelibreoffice-calc
MT---
MT---

Reputation: 1

Mass simplify simple equations in cells

A problem so simple I cannot find a solution.

I have a sheet of a few thousand numbers in Li. Many are just that, a string of a few digits as z, although some are formatted as x*y. I have spent the better part of two hours trying to find a way to get all the cells to compute and simplify to just z, meaning, to compute the multiplication.

Does anyone know how to do this? I know there has to be a simple solution, but with everything I've tried nothing seems to work (or in most cases do anything all). If you need any additional info just ask.

I've tried a lot, but I am swinging in the dark. I've used libreoffice off and on for a few years and have a rather surface level of experience (I know what are/how to use functions, basic formatting, etc.).

Upvotes: 0

Views: 40

Answers (2)

JohnSUN
JohnSUN

Reputation: 2539

@JimK is right - there are no built-in tools for performing this task in Calc, but the script can do it. A macro similar to this could handle the task:

Sub Str2Frmlas
Dim oActiveSheet As Variant
Dim oUsedRange As Variant
Dim oDataArray As Variant
Dim oFA As Object 
Dim r As Long, c As Long 
    oActiveSheet = ThisComponent.getCurrentController().getActiveSheet()
    oUsedRange = oActiveSheet.createCursor()
    oUsedRange.gotoEndOfUsedArea(True)
    oDataArray = oUsedRange.getDataArray()
    oUsedRange.clearContents(com.sun.star.sheet.CellFlags.HARDATTR)
    oFA = CreateUNOService("com.sun.star.sheet.FunctionAccess")
    For r = LBound(oDataArray) To Ubound(oDataArray)
        For c = LBound(oDataArray(r)) To Ubound(oDataArray(r))
            If Trim(oDataArray(r)(c)) <> "" Then
                If oFA.callFunction("REGEX",Array(Trim(oDataArray(r)(c)),"^[0-9\*\/\+]*$"))=Trim(oDataArray(r)(c)) Then oDataArray(r)(c) = "=" & Trim(oDataArray(r)(c))
            EndIf 
        Next c
    Next r
    oUsedRange.setFormulaArray(oDataArray)
    ThisComponent.calculate()
End Sub

Upvotes: 0

Jim K
Jim K

Reputation: 13819

For example, with the following strings in column A:

2
3*4

Select column A and go to Data → Text to Columns. Specify separated by Other * and press OK. Now it looks like this:

A   B
~~  ~~
2
3   4

Finally, enter this formula in column C and fill down:

=IF(B1<>"";A1*B1;A1)

The result:

C
~~~
2
12

For more complex expressions such as 2*3/4+5, you could write a macro to run the values through eval() in a language such as python. Note that eval() is notoriously insecure, so don't do that unless you either have full control of the data or else check for all potentially dangerous characters first, for example by requiring that the input matches a regex like /^[0-9\*\/\+]*$/.

Upvotes: 0

Related Questions