Evaluate text cell as a formula in Calc

English
#1

Hi,
I can’t find anywhere how to “evaluate” a text cell as a formula in Calc.
eg “25 * 30 * 60”
to be evaluated as “=25 * 30 * 60 / 1000”. (I could manage the /1000 in another cell indeed)

  • I need the text cell.
  • I just need what function to call to evaluate it as a formula.

It seems that this is a known issue/limitation in Calc that doesn’t exist in Excel ?.
I tried to look at INDIRECT, but it’s not even documented in the Calc User Manual, not found in LO help, and it doesn’t seem to do what I need.

#2

Let me repost my comments in IRC here.

There is no “evaluate” function in Calc. It may be implemented using a macro (esp. if you use Python); but why do you need that, at all? Often, when people want it, they actually can equally do the opposite, and have a normal cell with a =25 * 30 * 60 / 1000 formula, plus a cell with a =FORMULA(A1) formula referring to the first one.

Does Excel already implement an Eval?

#3

Will not help, but available:

https://wiki.documentfoundation.org/Documentation/Calc_Functions/INDIRECT

#4

Like as column F in this example ?

#5

See this “help”.
As always with MS Excel help I miss relevant informtion. In this case mainly

  • how are formulas treated if they have parts whose interpretation depends on the locale or user settings?
  • an explicit explanation concerning “What about formulas returning an array?”
  • a clear statement if the function can also be used in a spreadsheet or only in VBA for Excel.

Of course they don’t miss to tell you that you should “Buy Microsoft 365” (whatever this should mean - suppose Office 365).
=== ===
Editing:
I forgot the most relevant problem. It’s now in the first place.

#7

I will wait!

#8

For what?

Do you know?

#10

In Excel, this UDF function will evaluate expressions:
VBA:

Function Eval(txt)
   Eval = Application.Evaluate("=" & txt)
End Function

Having Python “under the hood” of LO allows you to create incomparably more powerful UDFs (see also @mikekaganski 's post above :slight_smile: )

1 Like
#11

This seems to include that the Evaluate() function of Excel VBA can’t be used in cell formulas directly.
Does it try to handle references in any way? (Can hardly believe.)
If references simply are disregarded, a function on board of Python may do.

And: I won’t start to study Python at 80.
It’s “under the hood” of LibO, but not of Lupp. There are a few additional reasons not only related to my age. However, I won’t start a new discussion about this “OT-topic” :grin: except on your visit in Munich - if one will be.
Just for fun:
evalGame.ods (25.5 KB)

1 Like
#12

VBA Eval works with MS Access, Application.Evaluate works with Excel.
“My” UDF function Eval above “understands” cell range references (practically the same syntax as in Excel formulas).
I really liked your “toy”. The same trick (with an assistant) can probably be used in other situations.

#13

How possible? Relative referencing needs to know the “base address”. A cell formula does. The same formula evaluated by user code can’t.

Years ago the “trick” came to my mind in a completely different context. To apply it possibly to the often requested pocket-calculator-trick was obvious, but I never wrote code for it. (No need, no convincing use-cases.) Now I did. For “PoCa” we might use a reserved cell/sheet within the current document, but in critical cases such means might influence results anywhere [SHEETS() e.g.].

#14

Evaluate is a method of the Application object, not a function. The Application object “knows” almost everything. Of course, my example is extremely simplified and will work correctly with cell references if the document contains one sheet. For a multi-sheet Excel workbook, more code is needed.

#6

i think to extract all the 20 charakters of that TEXT string into each position of number and operator – in unique resolutions because the math. operators are not equal to the digital operators and the values can be with or without comma/dot.

Now for the extraordinary result in a very complex universal extraction with many operators and fractional TEXT numbers in GBR/DEU notation. See the line in position #7, where I simplified the given problem (see formula above!) with multiplications. Here I only want to explain how TEXT can be transformed into a computable formula – step by step. It can certainly be simplified with desired restrictions or abbreviations if one only wants to calculate integers or fractional numbers in GBR or DEU notation. Note the usual different notation of foreign currencies: ‘10.000,00 €’ and ‘U$ 10,000.00’, respectively.
1_LO-CALC_REGAUS()_Ziffern+Zeichen-Text-String extrahieren nach mathematischer Formel_023649.ods (36.0 KB)
My non perfect expressions ignores the math. regulations using typically brackets!
Suitable for experimentation my high effort, which is generalized and can be easily simplified for special cases, because the effort only serves to distinguish the computer-typical “reading style” with our human understanding of words in reading a number: «one hundred and twenty-three» and our special mathematical reading style (123) and the computer resolution [1× ‘100’ + 2×’10’ + 3×’1’].

Don’t be afraid to ask if you don’t understand the complexity; I/someone else can break it down for you! I’m an imperfect, aging illustrator who makes obscure technology understandable for ordinary people.

At some point I will probably mathematically correctly unravel text with brackets in the afterlife. Because then I will have infinite time for it. Have a nice and beautiful life…

#9

like where ? :thinking:

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.