You are not the first one to post such a question somewhere, and as far as I know nobody posted a satisfying solution. LibO obviously isn’t just missing an EVALUATE function, but also the API does not provide a service for the purpose. (There is a service for parsing / tokenising a formula, however, but I don’t know how to create an instance of it or how to use it.)
Thus the only way I can think of is to use helper cells for the task and to write some user code to manage it.
Let me explain:
Suppose you have a sheet S1 and you created a (probably hidden) sheet S0.
In OtherSheet.D11 you have the TEXT
=FIND(°.A11;°.B11) and you want to get evaluated In
S1.X11 the formula
=FIND(S1.A11;S1.B11) created from the above text substituting a placeholder with a sheetname.
=GETRESULT(PASSCELLZYX(SHEET()+1;ROW();COLUMN());SUBSTITUTE($OtherSheet.D11;"°";"S1")) into X11 of S1.
Of course, nothing will happen except that an error is reported.
You need to define some user functions:
PASSCELLZYX() passes the needed info about the position of the helper cell as an Array(1 To 1, 1 To 3). How the formula string is passed is clear.
GETRESULT() finally has to access the helper cell, to set the passed string as its formula, to get the formula result, and to return it to the calling cell.
Yes. It’s so extremely simple.
You may play with the primitive version contained in this attachment.
Make sure to understand that I don’t encourage you to use crotches of the kind. I did a lot with Calc for a few decades now, and never had to finally resort to something like that. In fact I very rarely wrote user functions, and if I did they were mostly mathematical.
You should expect the described method to be rather inefficient.