how to calc 325+6 in a cell with out Equal

Question

A function that can compute an expression such as (2 * 5 + 6=16 or

10*3*0.75=22.5) without equating.

i did it with execl but it is not complate

=IFERROR(LEFT(G2170,FIND("*",SUBSTITUTE(UPPER(G2170),"C","*"))-1)*RIGHT(G2170,LEN(G2170)-FIND("*",SUBSTITUTE(UPPER(G2170),"C","*"))),IF(LEN(G2170)=10,LEFT(G2170,1)*MID(G2170,3,2)+MID(G2170,6,2)+MID(G2170,9,2),IF(LEN(G2170)=8,LEFT(G2170,1)*MID(G2170,3,3)+RIGHT(G2170,2),IF(LEN(G2170)=7,LEFT(G2170,1)*MID(G2170,3,2)+RIGHT(G2170,2),IF(LEN(G2170)=9,LEFT(G2170,2)*MID(G2170,4,1)*RIGHT(G2170,4),IF(LEN(G2170)=5,LEFT(G2170,1)*MID(G2170,3,1)*RIGHT(G2170,1)))))))

https://superuser.com/questions/253353/excel-function-that-evaluates-a-string-as-if-it-were-a-formula — Siddharth Rout, Dec 08 '21 at 13:46
https://stackoverflow.com/questions/4471884/how-to-turn-a-string-formula-into-a-real-formula — Siddharth Rout, Dec 08 '21 at 13:49

Pᴇʜ · Answer 1 · 2021-12-09T08:04:22.037

6

You can write an easy user defined function to evaluate those strings:

Option Explicit

Public Function EvaluateString(ByVal Formula As String)
    EvaluateString = Application.ThisCell.Parent.Evaluate(Formula)
End Function

The code needs to be in a Module.

It can be used as formula like EvaluateString(A1) and A1 can be any formula that Excel is able to evaluate. If the formula includes other formulas like SUM that contain words they need to be the english formula version no matter which localization your Excel uses as Evaluate only handles english formulas.

And you probably need to make this function Application.Volatile if the formulas contain references to other cells. Avoid that if there are no references for sure. Volatile functions will have a high calculation impact on your sheets (the get calculated everytime anything gets calculated on the sheet).

edited Dec 09 '21 at 08:04

answered Dec 08 '21 at 10:40

Pᴇʜ

56,719
10
49
73

3

FYI this uses the default `Application.Evaluate` which is fine if there are no cell references, but any cell references would always reference the ActiveSheet, so the result may be wrong when a different sheet is active. Maybe `Application.ThisCell.Parent.Evaluate()` might be safer (to force the context to the sheet with the UDF)? – Tim Williams Dec 08 '21 at 17:06
@TimWilliams Makes absolutely sense. I fixed the answer. Thank you. – Pᴇʜ Dec 08 '21 at 17:20
...true, indeed. C.f. post addition at [Further note to worksheet related evaluation](https://stackoverflow.com/questions/69774503/copy-last-3-char-of-text-in-one-column-to-another-column-if-cell-is-blank-in-exc/69780932#69780932) – T.M. Dec 08 '21 at 19:06

how to calc 3*2*5+6 in a cell with out Equal

1 Answers1

how to calc 325+6 in a cell with out Equal